Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Cross Reference Lookup

Posted by Mark Reynolds on January 29, 2002 8:19 AM
I'm hoping this is possible because it will really help me out. I want to do a lookup that looks across the top row of a table, then across the first column in a table and returns the value that is intersected by these two points. Any suggestions??

Check out our Excel Resources

Re: Cross Reference Lookup

Posted by Aladin Akyurek on January 29, 2002 9:00 AM
One way is by using the intersection operator (which needs computing with labels).

What follows is another method.

Lets say that A1:D4 houses the following sample:

{"","x","y","z";
"val1",10,21,33;
"val2",21,15,20;
"val3",24,17,21}

"" stands for a blank cell.

Suppose also that E1 houses the lookup value that involves the rows of the first column and E2 the lookup value that involves the columns of the first row. That is:

E1: val3
E2: y

In E3 enter: =OFFSET(A1,MATCH(E1,A2:A4,0),MATCH(E2,B1:D1,0))

will fetch the desired value from the table.

Aladin

========


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.