# Thread: Two Dimensional lookup (Excel 2007)

1. ## Two Dimensional lookup (Excel 2007)

I am trying to setup two dimensional lookup formula but i get error in the attached sheet. I request for some help from the gurus!!
Regards
Novice

2. ## Re: Two Dimensional lookup (Excel 2007)

If this is not specific to XL2007, dould you post a version that is saved to the earlier XL format for those of us without XL2007.

Steve

3. ## Re: Two Dimensional lookup (Excel 2007)

Hi Steve,
Attached worksheet is in excel 97-2003 format.
tks for the prompt response.
rgds
novice

4. ## Re: Two Dimensional lookup (Excel 2007)

I think the formula you want in E16 is:
<code>=VLOOKUP(D\$15,\$D\$5:\$I\$9,MATCH(D16,\$D\$4:\$I\$4, 0))</code>
then copy down.

5. ## Re: Two Dimensional lookup (Excel 2007)

Hi Rory,
I want the formula to give me the value at intersection of D15 and D16, thereafter D16, D17 and D17,D18 so on from the table.
novice

6. ## Re: Two Dimensional lookup (Excel 2007)

Oh, then you want:
<code>=VLOOKUP(D15,\$D\$5:\$I\$9,MATCH(D16,\$D\$4:\$I\$4,0 ),FALSE)</code>
in E16 and copy down.
HTH

7. ## Re: Two Dimensional lookup (Excel 2007)

Hi Rory,
I tried the formula as suggested by you but it didn't work. One way to solve the problem is to have all the labels in the first row and column in an ascending order, in which case the following Formula works =VLOOKUP(D15,\$D\$5:\$I\$9,MATCH(D16,\$E\$4:\$I\$4,)+1). However, it would be nice to have a formula which can give the correct data even if the first row and col labels are not in ascending order.

8. ## Re: Two Dimensional lookup (Excel 2007)

we must be talking at cross purposes then as this is what I thought you wanted.

9. ## Re: Two Dimensional lookup (Excel 2007)

Hi Rory,

Thanks for the prompt response. I realsise that i was not copying the correct formula. Your worksheet and the formula indeed works and solves my problem.
Really appreciate your patience with a novice like me
rgds/novicexcelite

10. ## Re: Two Dimensional lookup (Excel 2007)

Glad to help. We're all novices at something! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

#### Posting Permissions

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