Lookup first two columns in Book1 and Match the corresponding Cluster in the second tab??

[Madhuri_G]

Can you please solve this scenario?? Attached the spreadsheet. Here postal codes are same but cities are different and clusters too.. We need to match exact scenario Please have a look and help me please

 

[cyliyu]

Is this what you want?

=IFERROR(INDEX(Sheet1!D:D,MATCH(1,INDEX(($A2=Sheet1!$A:$A)*($B2=Sheet1!$B:$B),0),0)),"No Data Found")

 

[Madhuri_G]

Yes exactly, thank you cyliyu make sense !

 

[bosco_yip]

Yes exactly, thank you cyliyu make sense !

1] In post #.2 D2, formula :

=IFERROR(INDEX(Sheet1!D:D,MATCH(1,INDEX(($A2=Sheet1!$A:$A)*($B2=Sheet1!$B:$B),0),0)),"No Data Found")

Don't used whole column reference (highlighted red), it is one million cells per column, too much cells calculation will cause your computer speed slowing down.

2] Use limited cell range, and in D2, formula copied down:

=IFERROR(INDEX(Sheet1!$D$1:$D$500,MATCH(1,INDEX(($A2=Sheet1!$A$1:$A$500)*($B2=Sheet1!$B$1:$B$500),0),0)),"No Data Found")

Regards
Bosco

 

[Madhuri_G]

Yeah,,you're right!! thanks for ur help Bosco !!

 

[Madhuri_G]

@cyliyu , @bosco_yip

The formula didnt worked for me,, all the data is coming as "Not found",Is there something wrong with my formula?? I have attached the file, the first sheet has 'accounts' tab- The column "S" & "T" of accounts sheet should match with "A" and "B" of Sheet2.. If matches then display column "D" over to accounts formula tab "AK" highlted..

Please help??

 

[cyliyu]

Please see attach.
The Postal City in Sheet2 have an additional space. .e.g. "SEDDON " is not the same as "SEDDON"

 

[Madhuri_G]

@cyliyu Thank you so much,,it means a lot to me!! appreciated for quick help.. You're a star

 

[cyliyu]

Pls note that I make changes to your formula too.

 

[NPOKALA]

Can you please solve this scenario?? Attached the spreadsheet. Here postal codes are same but cities are different and clusters too.. We need to match exact scenario Please have a look and help me please

Please copy it in D2 cell and drag down to see matching cluster details:

=IF(Sheet1!A2&Sheet1!B2=A2&B2,Sheet1!D2,"")

 

[NPOKALA]

@cyliyu Thank you so much,,it means a lot to me!! appreciated for quick help.. You're a star

Hi
try this simple method:

=IF(Sheet1!A2&Sheet1!B2&Sheet1!C2=A2&B2&C2,Sheet1!D2,"")