Vlookup Help

[modmac]

Hello,

I'm trying to use vlookup to look through 2 columns and return the valve in the 3rd column.

DATA:

"A" "B" "C"
tht1234 tht01234 35.7
kit0922 kit00922 456

Here is the fourmula I'm using, I can only get it to find the value from column "A" but not "B".

=IF(ISNA(VLOOKUP(A10,PARTS!$B$2:$C$25080,2,0)),"",VLOOKUP(A10,PARTS!$B$2:$C$25080,2,0))

 

[AnotherHiggins]

VLookup doesn't work the way you want. But there are some workarounds.

[tab]1 - Concatenation
Insert a helper cell between B and C. Join together the data in A and B using =A2&B2. Do the same thing on the Parts sheet. Now you can lookup using the concatenated fields.

[tab]2 - SumProduct
If you want to return the value from column C where both columns A and B match your criteria, you could use something like:
[COLOR=blue white]=SumProduct( --(PARTS!$A$2:$A$25080 = A10) * --(PARTS!$B$2:$B$25080 = B10) * --(PARTS!$C$2:$C$25080))[/color]

To find out more about how SumProduct works, see here

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi modmac:

Using MATCH and INDEX functions, you can use the follwing array fornula ...

=INDEX(Parts!C2:C25080,MATCH(A10&B10,Parts!A2:A25080&Parts!B2:B25080,0))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[modmac]

Thanks for responding, but I'm not sure I explainined it well enough. On sheet 1 I will enter a value of either tht01234 or tht1234 in a cell (A2), I want it to look through columns A & B on sheet 2 and if it listed give me the value in column c, if not return a 0.


Sheet 2

A B C
tht1234 tht01234 35.70
dhy0297 dhy00297 12.50

 

[AnotherHiggins]

Well, it ain't pretty, but this will do what you want:

[COLOR=blue white]=if(isna(vlookup(A1, Parts!A2:C5 , 3, 0)), "", vlookup(A1, Parts!A2:C5, 3, 0)) & if(isna(vlookup(A1, Parts!B2:C5, 2, 0)), "", vlookup(A1, Parts!B2:C5, 2, 0))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

Maybe I am missing something but I reckon your original formula was not far off. change the red bits and delete the blue:
=IF(ISNA(VLOOKUP(A10,PARTS!$[red]A[/red]$2:$C$25080,[red]3[/red],0))[blue],""[/blue],VLOOKUP(A10,PARTS!$B$2:$C$25080,2,0)[red],""[/red])

Regards

Gavin

 

[yogia]

Hi modmac:

Let me see if I have understood you correctly this time around. Try array the following array formula ...

=MIN(IF(ISNUMBER(FIND(A2,Sheet2!A2:A5&Sheet2!B2:B5)),Sheet2!C2:C5))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Rofeu]

you can use the following:

=OFFSET(Sheet2!$C$1,SUMPRODUCT(--(Sheet2!$A$2:$A$5=$A$1)+--(Sheet2!$B$2:$B$5=$A$1),ROW(Sheet2!$C$2:$C$5)-1),0)

Cheers,

Roel