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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,222
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
12,222
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,606
Messages
5,838,340
Members
430,538
Latest member
PedroOliveira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top