Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup - Using a named ranged for col_index_num
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 |
#2
|
|||
|
|||
=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 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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup with concatinated named range | Excel Worksheet Functions | |||
VLOOKUP Function using Data Ranges. | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
vlookup and named ranges | Excel Worksheet Functions | |||
vlookup and named ranges | Excel Worksheet Functions |