Lookup on 2 different items

[Aerowolf]

Here's what I've got:

A B C
1 PART1 1 ONEONE
2 PART2 1 TWOONE
3 PART3 3 THREETHREE
4 PART3 1 THREEONE
5 PART4 1 FOURONE
6 PART5 1 FIVEONE

Assume that G1 = PART3
Assume that G2 = 1

I want to be able to lookup G1 in Column A, then lookup G2 in column B starting in the row that resulted from the first lookup, and use the corresponding value in column C as my result. Column A will always be in order but there may be times where a value in that column is repeated, as above with PART3. If there are more than one of a certain part, I need to look for G2 to determine which of that part I need.

I was hoping to do this with a nested VLOOKUP formula like this:

VLOOKUP(G2,B(row of lookup for G1):C6,3)

but I don't know how to get the ROW value of the first lookup.

Any thoughts?

Edwin

 

[xlbo]

Lookup doesn't return a range, it returns a value. the simlest way to do this is to have a hidden column (col C) so that what is in Col c moves into col D
In col C, you would use the formula
=A1&B1
and copy it down

your lookup formula would then be
=vlookup(G1&G2,$C$1:$D$6,2,false)


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Aerowolf]

Thanks very much Geoff...works perfectly!!

Edwin

 

[DrBowes]

Or if you want to pose:

=VLOOKUP(G2,(INDIRECT("B"&MATCH(G1,A1:A6,0)&":c6")),2,FALSE)

 

[bluedragon2]

DGET is another function you can use. Can look up multiple criteria.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GoDawgs]

And the SumProduct function...I've noticed a recurring theme on this site.

 

[bluedragon2]

The SUMPRODUCT is great for getting numerical counts and sums, but will not return a text value.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GoDawgs]

Oops, my fault...didn't think about that...