Partial Match in Excel

0
votes

I have an excel workbook with two sheets with hundreds of row in each sheet.

Sheet1 contains "ABCESX42 - Port 1" in Cell A2.

enter image description here

Sheet2 contains "ABCESX42" in Cell A2 and "EMAIL" in B2.

enter image description here

I am trying to go a VLOOKUP in Sheet1 Cell B2 to do a wildcard vlookup on Sheet1 A2 to partial match against any entry in Sheet2 Column A and output Email into Sheet1 B2

I hope this makes sense.

Thanks Anthony.

1
excelvlookuppartial
This is tough, because you are going the other way. You may need to use a VB script to search though the lookup table match those to your input string. This will give you the index, then you can get the column value for the row in the lookup table... – Mr. Polywhirl

1 Answers

0
votes

use this in B2:

=INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(Sheet2!$A$1:$A$2)/(ISNUMBER(SEARCH(A2,Sheet2!$A$1:$A$2))),1))