vlookup function

[bill miller]

6-column, 9-row spreadsheet.
columns represent quantities.
rows represent product types.

Trying to create a function which will return a specific
cell value regardless of the column location.

vlookup requires a specific "target" column.

I need to enter product type AND quantity to determine
the discount applicable to those two particular
circumstances.

PLEASE HELP!!! (my hair is almost gone! ha! ha!)

Thanks, bill

 

[Paul Corrado]

Bill,

Use MATCH to find the appropriate column based on the column headings.

PC

 

[Alan Beban]

Insure that you have row and column headings that are suitable as range
names. Then highlight the data range, including the headings, and click
on Insert|Name|Create and check Top row and Left column and click OK.
Now =rowheader columnheader will return the value at the intersection.

Alan Beban

 

[Bob Phillips]

Bill,

Assuming the data is in A1:F9, product name in A2:A9, quantity in B1:F1,
then try this

=INDIRECT(CHAR(MATCH(G1,B1:F1,0)+1+64)&MATCH(G2,A2:A9,0)+1)

where G1 and G2 hold the quantity and product to look up.

 

[Adrian]

Try this:
=INDEX(DataRange,MATCH(Product_Input,Products,0),MATCH(Qty_Input,Quantities,
1))

HTH.