two sets of variables to find result

[Annette]

I can't seem to find the right formula for this formula.

A2:A100 = product code
B2:B100 = price (level 3)
C2:C100 = price (level 2)
D2100 = price (level 1)

If I enter a product code in cell E1 and enter "Level 3" in cell F1, I want
the formula in cell G1 to tell me what the price of that product is in the
column B for that level.

I hope this makes sense and someone can help me as I know I have used this
before.

Thanks!

Annette

 

[Elkar]

You could try something like:

=VLOOKUP(E1,A2100,LOOKUP(F1,{"Level 1","Level 2","Level 3"},{4,3,2}),FALSE)

HTH
Elkar

 

[Bob Phillips]

=INDEX(B2100,MATCH(E1,A2:A100,0),MATCH(F1,{"Level 1","Level 2","Level
3"},0))

 

[Annette]

Perfect ... thanks much!

You could try something like:

=VLOOKUP(E1,A2100,LOOKUP(F1,{"Level 1","Level 2","Level
3"},{4,3,2}),FALSE)

HTH
Elkar