VLOOKUP challenge

[Jovinchious]

Hello everyone,

I need a bit of help.

I need some formula that will make me the following…

if the text in the A column of "sheet1" is the same as the text anywhere in the A column of "sheet2", and also if the text in the B column of "sheet1" is the same as the text anywhere in the B column of "sheet2",
then return the value from the D column of "sheet2"
So, both conditions must be satisfied, so that program can return the value from column D only if the names in column A and in column B match.
The one small challenge is that sometimes names in A and B column are the same, but number from D column is unique. In that case, program should give any number and not repeat the same number twice.

example:

Sheet1

A B D
Calculator Central ?
Calculator Central ?
Keyboard Informatics ?
Keyboard Informatics ?
Keyboard Central ?

Sheet2

A B D
Calculator Central 5865685
Calculator Central 9967967
Keyboard Informatics 2223423
Keyboard Informatics 5445345
Keyboard Central 6346333

I ve some of the following formulas but cant get it done...
=IF(COUNTIFS(Sheet2!A:A; A88; Sheet2!B:B; B88)>0; INDEX(Sheet2!D:D; MATCH(A88&B88; Sheet2!A:A&Sheet2!B:B; 0)); "")
=IF(SUMPRODUCT((Sheet2!A:A=A88)*(Sheet2!B:B=B88)*(Sheet2!D:D))>0; INDEX(Sheet2!D:D; MATCH(1; (Sheet2!A:A=A88)*(Sheet2!B:B=B88)*(Sheet2!D:D<>""); 0)); "")
=INDEX(Sheet2!$D:$D;MATCH(1;(Sheet1!$A:$A=Sheet2!$A:$A)*(Sheet1!$B:$B=Sheet2!$B:$B);0))

Thanks in advance!

 

[duggie33]

Hello,

If I understood your challenge correctly, this should work with the appropriate changes to match your data locations:

=IF(ISNA(MATCH(A7&B7,Sheet8!$A$1:$A$18&Sheet8!$B$1:$B$18,0)),"Not found",INDEX(Sheet8!$D$1:D24,MATCH(A7&B7,Sheet8!$A$1:$A$18&Sheet8!$B$1:$B$18,0)))

I used MATCH to find the row where both criteria were found.

Hope that helps,

Doug

 

[Jovinchious]

thank you! I found another way simplier way...just sticked together text from A and B column and did it with simple vlookup

=IF(COUNTIF(Sheet2!$A:$A; A17)>1; "DUPLICATE";VLOOKUP(A17; Sheet2!$A:$E; 5; FALSE))

 

[duggie33]

Nice.