Multiple Search and Return Formula

[LearningExcel101]

I have been struggling with this one for a couple days now. I need to match the first 21 characters in a cell to a list on a separate tab, and if there is a match, return the value in a different column from the same row back to the original tab. Cant get a VLOOKUP to work, please help.

Thank you

Example Book:
(cant download xl2bb on work computer)

Compare 'Sheet 2' cell 'A2' first 21 characters in the cell to find a matching first 21 characters on 'Sheet1' B2:B14 range and then return Sheet 1's 'A2' value to Sheet 2's "B2".

Sheet 2:

Sheet 1:

 

[Kaper]

VLOOKUP wouldn't do it as it requires search-in column to be leftmost, and return-from column to be rigth from it. As you are using Microsoft 365 you can use XLOOKUP instead:

=XLOOKUP(LEFT(A2,23),LEFT(Sheet1!$B$2:$B$20,23),Sheet1!$A$2:$A$20)

In old Excel versions nested INDEX and MATCH functions could be used. Probably this:

=INDEX(Sheet1!$A$2:$A$20,MATCH(LEFT(A2,23),LEFT(Sheet1!$B$2:$B$20,23),0))

or

=INDEX(Sheet1!$A$2:$A$20,MATCH(LEFT(A2,23)&"*",Sheet1!$B$2:$B$20,0))

shall do the job

 

[Peter_SSs]

Another XLOOKUP possibility

=XLOOKUP(LEFT(A2,21)&"*",Sheet1!B$2:B$20,Sheet1!A$2:A$20,,2)

Or if you want to use VLOOKUP

=VLOOKUP(LEFT(A2,21)&"*",CHOOSECOLS(Sheet1!A:B,2,1),2,0)

 

[Kaper]

PS. of course in formulas proposed above use LEFT(... , 21) somehow I changed it to 23

 

[LearningExcel101]

Both options work great, thank you!! @Kaper & @Peter_SSs

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.