VLookup

[Scott]

Is there any way to have vlookup use two variables to do the look up and return a value that comes from a third column, ie
Number Quantity Price/shee
1 25 1
1 50
1 75
1 100
2 25 9.7
2 50 8.7
2 75 7.7
2 100 6.7
3 25 8.5
3 50 7.5
3 75 6.5
3 100 5.5

In other words I would start looking a value up in the first column then looking up the value in the second column to return the value in the third column.

 

[Jason Morin]

Of course there is. Assuming Number in col. A, Quantity in
col. B, and Price/sheet in col. C:

=INDEX(C1:C10,MATCH(D1&E1,A1:A10&B1:B10,0))

where D1 hold the Number criterion and E1 holds the
Quantity criterion.

This is an array, so after inserting the formula, you must
press <ctrl><shift><enter>.

HTH
Jason
Atlanta, GA

-----Original Message-----
Is there any way to have vlookup use two variables to do

the look up and return a value that comes from a third
column, ie.

Number Quantity Price/sheet
1 25 10
1 50 9
1 75 8
1 100 7
2 25 9.75
2 50 8.75
2 75 7.75
2 100 6.75
3 25 8.50
3 50 7.50
3 75 6.50
3 100 5.50

In other words I would start looking a value up in the

first column then looking up the value in the second
column to return the value in the third column.

 

[Peo Sjoblom]

One way

=INDEX(Price_Range,MATCH(1,(Number_Range=2)*(Quantity_Range=50),0))

entered with ctrl + shift & enter

if using cell refs it could look like

=INDEX(D2200,MATCH(1,(B2:B200=2)*(C2:C200=50),0))

now a good thing would be to replace 2 and 50 in this example with 2 cell
reference where you
type in the criteria, like

=INDEX(D2200,MATCH(1,(B2:B200=G1)*(C2:C200=H1),0))
--

Regards,

Peo Sjoblom

Is there any way to have vlookup use two variables to do the look up and

return a value that comes from a third column, ie.

Number Quantity Price/sheet
1 25 10
1 50 9
1 75 8
1 100 7
2 25 9.75
2 50 8.75
2 75 7.75
2 100 6.75
3 25 8.50
3 50 7.50
3 75 6.50
3 100 5.50

In other words I would start looking a value up in the first column then

looking up the value in the second column to return the value in the third
column.