hlookup and vlookup

Posted by Coline on January 18, 2002 12:40 PM

Hi, I would like to use lookup commands to match row and column headings simultaneously to return a value in one cell. Is there an easy way to do this without using multiple formulas?
Thanks

Posted by bob Umlas on January 18, 2002 12:46 PM

You need index/Match: assuming the rows are Jan Feb, ..., and the columns are Part1, part2,... and the values to lookup are part2 and Feb,
=INDEX(Table,match("part2",index(table,,1),0),match("Feb",index(table,1,),0)) will give the intersection.
Of course, if part2 is in cell E1 and Feb in cell E2, you can use:
=INDEX(Table,matchE1,index(table,,1),0),match(E2,index(table,1,),0))

Note: Index(table,,1) is the first column or table and index(table,1,) is first row of table.

Also assumed that "table" is a defined name representing the cells to be examined.

Posted by Coline on January 18, 2002 1:03 PM

Thanks! It works like a charm!