Lookup Tables

Posted by Allan on January 02, 2002 12:44 PM

I hope someone can help. I want to find a value in a table referencing information from a column and a row, so you can't use Vlookup or Hlookup as far as I can tell. For example I have a table that gives the number of reel changes on a print machine that varies due to page size (in a column) and batch size (in a row), the resultant table has varying numbers in it. So I want to reference the Page Size and the Batch size and get the relevent value from the table to use in another spreadsheet to calculate reel change waste.

I hope this problem makes sense as it seems simple but I've searched everywhere (including Microsoft!) but can't get an answer.

Posted by Scott on January 02, 2002 12:51 PM

Try this. This assumes your data is in B4:F8, and that the column indicator is in A14 and the row is in B14.

=VLOOKUP(A14,B4:F8,MATCH(B14,B4:F4),0)

The columns for the table would be in B and the row headings in row 4.

Posted by Aladin Akyurek on January 02, 2002 1:00 PM

Allan --

There are a few ways to compute (retrieve) what you want.

Lets say that you have your table in A1:D4 where B1:D1 contains the column labels/headings and A2:A4 the row labels/headings:

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

where E1 houses key/lookup value wrt rows and E2 key/lookup value wrt columns, will retrieve the value of interest.

Aladin

=========

Posted by Allan on January 03, 2002 12:50 AM

Aladin,

Thanks, it works exactly as I wanted! All the best for 2002. Regards, Allan