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
