problem using wildcards in INDEX / MATCH formula

I am trying to work out a spreadsheet to fill in my monthly charges from my bank statement.

I have columns for months and rows for different charges.

My data has columns for months, charges and amounts.

I want to fill the amount into my spreadsheet.

The challenge I have is that the charge descriptor in the data has a lot of extraneous bank gibberish. I want to search the cell for the keyword I need (for example, "Discover" for my Discover card bill.

Here is the formula I have that works if I strip out the charge fields to just the keyword I want.

=INDEX($E$2:$E$334,MATCH(1,('2018 Table'!B$1=$A$2:$A$334) * ('2018 Table'!$A$2=$D$2:$D$334),0))

Column E is the amounts, column A is the month and column D is the charge description.

I modified the formula like this to try and get the wildcards to work -

=INDEX($E$2:$E$334,MATCH(1,('2018 Table'!B$1=$A$2:$A$334) * ("*Discover*"=$D$2:$D$334),0))

but it didn't work.

I tried this

=INDEX($E$2:$E$334,MATCH(1,('2018 Table'!B$1=$A$2:$A$334) * ('2018 Table'!$A$2&"*"=$D$2:$D$334),0))

and it also didn't work.

I also tried "*"&'2018 Table'!$A$2&"*"
with no luck.

Could the problem be that I am trying to compare two arrays and that wildcards don't work when using MATCH with two criteria?

Or am I missing something?

Thanks for any help you can provide.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hello edteitz. Welcome to the forum.

Have a look at the MATCH function help file.

MATCH(lookup_value, lookup_array, [match_type])

Wild cards only apply to the lookup_value.

If the lookup_value you are using also contains some of the "gibberish" ... eg "****" ... you will need to take extra steps

##If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.[emphasis added]

Does any of this help?

Are you looking for this?

Try this:

=INDEX($E$2:$E$334,MATCH(1,('2018 Table'!B$1=$A$2:$A$334) * (ISNUMBER(SEARCH('2018 Table'!$A$2,$D$2:$D$334))),0))

Withdrawn.

Rory beat me to it.

You are welcome. Glad to help ... (or sort of help LOL )

Thank you for the feedback and marking your thread Solved.

Couldn't say without a representative workbook upload that includes the malfunctioning formulas.

If you are not familiar with how to do that:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

It's not a bug. Wildcards don't work with =, that's just the way it is.