Index Match with Wildcard

[Traveltime]

A Line Description-Located on 1st Worksheet	B Expense Type - Located on 1st Worksheet	C Vendor Name - Located on 2nd Worksheet	D Type - Located on 2nd Worksheet
AMEX Reclass - 35965 (these number change)	Results goes from column D	AAA-AAA	Taxes
BBB-BBB	The result would be Earnings from Column D	BBB-BBB	Earnings
AMEX Reclass - 35784 (These numbers change)	The result would be Award from column D	AMEX RECLASS	Award
AMEX - 57896 (These numbers change)
AAA-AAA	The result would be Taxes from Column D

<tbody>
</tbody>

Column C and D is a table in separate worksheet from columns A and B. I need an index match to search column C and the corresponding row from Column D in Column B of the first worksheet. There isn't an exact match between column A and C so I think I need a wildcard.

Hopefully this makes sense.

Thank you!

 

[Eric W]

Maybe something like this:

	A	B	C	D
1	Line Description-Located on 1st Worksheet	Expense Type - Located on 1st Worksheet	Vendor Name - Located on 2nd Worksheet	Type - Located on 2nd Worksheet
2	AMEX Reclass - 35965 (these number change)	Award	AAA-AAA	Taxes
3	BBB-BBB	Earnings	BBB-BBB	Earnings
4	AMEX Reclass - 35784 (These numbers change)	Award	AMEX RECLASS	Award
5	AMEX - 57896 (These numbers change)
6	AAA-AAA	Taxes

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =IFERROR(LOOKUP(2^16,SEARCH(Sheet2!$C$2:$C$4,A2),Sheet2!$D$2:$D$4),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Traveltime]

Thanks for the reply Eric.

I didn't receive an error or N/A but it returns an empty cell rather than the match in column D of the table.

 

[Eric W]

The IFERROR on the formula will prevent any error from showing up. I'm at a loss why the formula doesn't work for you. The basic idea with the B2 formula is that it looks down the values in column C, and sees if any of them are found in A2. Since it found "AMEX RECLASS", it returned the value from the equivalent D column, D3=Award.

So if you changed the values from column C so they don't match anything in column A, you'd get an empty cell.