Index Match with Wildcard

Traveltime

New Member
Joined
May 21, 2018
Messages
17
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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,243
Maybe something like this:

ABCD
1Line Description-Located on 1st WorksheetExpense Type - Located on 1st WorksheetVendor Name - Located on 2nd WorksheetType - Located on 2nd Worksheet
2AMEX Reclass - 35965 (these number change)AwardAAA-AAATaxes
3BBB-BBBEarningsBBB-BBBEarnings
4AMEX Reclass - 35784 (These numbers change)AwardAMEX RECLASSAward
5AMEX - 57896 (These numbers change)
6AAA-AAATaxes

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

Worksheet Formulas
CellFormula
B2=IFERROR(LOOKUP(2^16,SEARCH(Sheet2!$C$2:$C$4,A2),Sheet2!$D$2:$D$4),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Traveltime

New Member
Joined
May 21, 2018
Messages
17
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,243
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,542
Messages
5,529,438
Members
409,877
Latest member
DDhol
Top