Use one of the following formula:
=INDEX(A1:E4,MATCH(G1,A1:A4,0),MATCH(H1,A1:E1,0))
OR
=VLOOKUP(G1,A1:E4,MATCH(H1,A1:E1,0),FALSE)
Assume: A1:E4 stores the whole table
G1 stores item number you want to find price: 1,2 or 3
H1 stores categ number you want to find price: 120, 143, ...
A1:A4 stores blank, 1, 2 , and 3 (item list)
A1:E1 stores blank, 120, 143, 150, 160 (category #)
===== * ===== * ===== * =====
Daniel CHEN
Spreadsheet/VBA Specialist
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====
wrote in message
oups.com...
Hi,
I have different numbered catalogs. I also have a spreadsheet showing
the item price on the particular catalogs.
120 143 150 160
item 1 $20 $30 $35 $34
item 2 $30 $40 $40 $35
item 3 $40 $50 $45 $39
I get catalogs every month where I have to do a vlookup to check that
the prices on the catalog match the one's on the spreadsheet above.
When I do the vlookup function, I would like to identify the catalogs
by their name (number) and not by the column index number when I input
the col_index_num of the vlookup function.
I want the col_index_num to indicate that if the Catalog is #120, then
the information to bring back is from column 2 (column with prices for
catalog #120.
Maybe I should use an IF function? if catalog is 120, then
col_index_num is 2? if catalog is 143then col_index_num is 3?...but how
do I write that in excel?
Please help,
Thanks,
Mike