1. ## Lookup? (2002)

Worksheet 1 - I have a pivot table that is summarizing spend by department. In column A it has department number and in column B it has class code. In column c it has acutal spend.

Worksheet 2 - On another spreadsheet, I have department numbers in the individual columns beginning in row C through AA and in column B I have expense class code per above. In the rows below each department number the actual spend is listed.

I would like to be able to, using the department number on worksheet 2, pull the actual spend by class code into column C on worksheet 1.

I will attach a sample.

2. ## Re: Lookup? (2002)

In D2, enter the formula

=IF(ISNA(INDEX(Worksheet2!\$C\$2:\$D\$3,MATCH(B2,Works heet2!\$A\$2:\$A\$3,0),MATCH(A2,Worksheet2!\$C\$1:\$D\$1,0 ))),"",INDEX(Worksheet2!\$C\$2:\$D\$3,MATCH(B2,Workshe et2!\$A\$2:\$A\$3,0),MATCH(A2,Worksheet2!\$C\$1:\$D\$1,0)) )

Fill down as far as needed.

3. ## Re: Lookup? (2002)

Hans, I have attached the actual worksheet. I tried the formula with no luck.

Can you help?

MJ

4. ## Re: Lookup? (2002)

Does the attached do what you want? I have adjusted the range references accordingly and also converted the values in column A on Worksheet1 from text to numbers.

5. ## Re: Lookup? (2002)

Works great. I multiplied all number values by 1. Don't understand why this was need but THANKS. Appears to work great. Where could I look to understand how this beautiful formula works?

6. ## Re: Lookup? (2002)

Let's look at the formula in D2:

=IF(ISNA(INDEX(Worksheet2!\$C\$2:\$AH\$52,MATCH(B2,Wor ksheet2!\$A\$2:\$A\$52,0),MATCH(A2,Worksheet2!\$C\$1:\$AH \$1,0))),"",INDEX(Worksheet2!\$C\$2:\$AH\$52,MATCH(B2,W orksheet2!\$A\$2:\$A\$52,0),MATCH(A2,Worksheet2!\$C\$1:\$ AH\$1,0)))

The essential part is

INDEX(Worksheet2!\$C\$2:\$AH\$52,MATCH(B2,Worksheet2!\$ A\$2:\$A\$52,0),MATCH(A2,Worksheet2!\$C\$1:\$AH\$1,0))

If we indicate this with A, the formula is =IF(ISNA(A),"",A): if A results in #N/A (not available), then return an empty string "", else return the result of A.

A says: take the range Worksheet2!\$C\$2:\$AH\$52, and look up the value in the row indicated by MATCH(B2,Worksheet2!\$A\$2:\$A\$52,0) and the column indicated by MATCH(A2,Worksheet2!\$C\$1:\$AH\$1,0).

MATCH(B2,Worksheet2!\$A\$2:\$A\$52,0) looks up the value of B2 in Worksheet2!\$A\$2:\$A\$52 and returns the index of the first match; the 3rd argument 0 specifies that the match must be exact.
Similar for MATCH(A2,Worksheet2!\$C\$1:\$AH\$1,0).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•