INDEX MATCH with MID or REPLACE

**Emile du Toit** · 06-12-2017, 10:44 AM

Hello!

I am trying to use a standard INDEX MATCH formula but where it only matches the cell data (matched cell) from the 7th digit to the end of the string (variable length), to a matching cell in a designated column where the data also only needs to match from the 7th digit to the end of the string (variable length) for all cells in that column.

I tried this:
=INDEX('EEU Final'!$C$2:$C$6000,MATCH(REPLACE(O2,1,6,""),REPLACE('EEU Final'!$O$2:$O$6000,1,6,""),0))

But clearly I havn't quite landed it. By the way i tried a different attempt with MID but that didn't work either.

Any help would be greatly appreciated!

Thank you in advance.

Warm regards,
Emile

**jeffreybrown** · 06-12-2017, 10:57 AM

Can you attach a small sample of what you have and what you expect?

Attach a sample workbook. 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.

**Emile du Toit** · 06-12-2017, 12:23 PM

Hi Jeff, sorry my columns got mangled in my last attempt to paste from Word. Please ignore the dashes - they are just space keepers.

Sheet 1

a-----------------------B--------------C---------------D
FTFTFT65TR----------77
FTFTFTUI876---------34
FTFTFT0009TYU------49
FTFTFTIR55----------99

Sheet 2
A-----------------------B--------------C---------------D
XYZXYZIR55-----------?
XYZXYZ65TY
XYZXYZTREE98

I am using INDEX MATCH to find B2 in sheet 2, by excluding the first 6 digits of A2 (Sheet 2) as well as the first 6 digits of Sheet 1 column A. So the formula for Sheet2 B2 should return 99.

**jeffreybrown** · 06-12-2017, 01:37 PM

Try in B2 of Sheet2...

=IFERROR(INDEX(Sheet1!$B$1:$B$4,MATCH(MID($A1,7,99),MID(Sheet1!$A$1:$A$4,7,99),0)),"")

IMPORTANT

This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

**Emile du Toit** · 06-12-2017, 02:24 PM

Thanks so much Jeff! Would you believe that I actually had got it correct at one stage using MID(without the ISERROR part), but whenever I hit 'ENTER' it showed BLANK. I didn't realize that this was an ARRAY formula! I must clearly read up more on what they are, as i understand that they have to do with when excel has to do one calculation before another, but I had no idea this was one of those situations. I actually think this might explain other incidences with formulas when Excel didn't register an error but insisted on giving the value as BLANK.

Anyhow, so DOUBLY Thank You!

**jeffreybrown** · 06-12-2017, 02:29 PM

You are very welcome. Glad it worked out for you!

INDEX MATCH with MID or REPLACE

LinkBack

Thread Tools

Rate This Thread

Display

INDEX MATCH with MID or REPLACE

Re: INDEX MATCH with MID or REPLACE

Re: INDEX MATCH with MID or REPLACE

Re: INDEX MATCH with MID or REPLACE

Re: INDEX MATCH with MID or REPLACE

Re: INDEX MATCH with MID or REPLACE

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] using Index match in replace of vlookup

[SOLVED] How to replace VLOOKUP with INDEX and MATCH

Index, Match, Left & or Replace needs refining

Match and replace with index

Match, Index, Data replace.

Replace #N/A using Index Match function

index/match/replace

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions