vlookup function

  • Thread starter Thread starter bill miller
  • Start date Start date
B

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
 
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
 
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.
 
Try this:
=INDEX(DataRange,MATCH(Product_Input,Products,0),MATCH(Qty_Input,Quantities,
1))

HTH.
 
Back
Top