Vlookup - Using a named ranged for col_index_num

[marin_michael]

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

 

[N Harkawat]

=VLOOKUP(A9,A4:E6,MATCH(143,A2:E2,0),0)

where cell A9 holds "item 2)
and
A2:E2 holds the catalog # (in your example 120,143,150..
A4:E6 holds the data with col A containing the item, # and col B through
Col E containing the pricing data

 

[Daniel CHEN]

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
(e-mail address removed)
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====