Index Match Formula Issie

[bradjedis]

Greetings,

This is an off-shoot of a recent question I posed regarding Index Match formula.

Formula I am trying:

=INDEX('E-Drive C data'!$B$1:$B$95714, MATCH(SUBSTITUTE(MID(C2, 2, 100), "~", "~~"), MID('E-Drive C data'!$C$1:$C$95714, 2, 100), 0))

Data format is as such:
Folder Path File Name

Source data: Col B: D:\abc\c-drive\abc123 Col C: abc.def

Target data: Col B: E:\abc\c-drive\abc123 Col C: abc.def

I want to match the data in col B then retrieve the data in col C assuming folder path is found and file name matches

THanks,
Brad

[bradjedis]

Got it!


WAS:
=INDEX('E-Drive C data'!$B$1:$B$95714, MATCH(SUBSTITUTE(MID(C2, 2, 100), "~", "~~"), MID('E-Drive C data'!$C$1:$C$95714, 2, 100), 0))

IS:
=INDEX('E-Drive C data'!$C$1:$C$95714, MATCH(SUBSTITUTE(MID('D-Drive C Data'!C2, 2, 100), "~", "~~"), MID('E-Drive C data'!$C$1:$C$95714, 2, 100), 0))

Played around a bit with the formula...