VLookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
Click to expand...
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
Click to expand...
first column then looking up the value in the second
column to return the value in the third column.
 
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(D2:D200,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(D2:D200,MATCH(1,(B2:B200=G1)*(C2:C200=H1),0))
--

Regards,

Peo Sjoblom


Scott said:
Is there any way to have vlookup use two variables to do the look up and
Click to expand...
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
Click to expand...
looking up the value in the second column to return the value in the third
column.
 
Back
Top