Index Match using MID Function

[mkron]

Hello,

I am trying to grab reference drawing numbers from another sheet using a design id.
In the workbook that I want to add the drawing number to, the design id is formatted as FC1234567899 and in the workbook where I am matching, the design id is formatted as 12345678.

The current formula I have is :
=INDEX(Storage_Reference_Assy.xlsm!Table1[#Data], MATCH(MID([@[Design ID]], 3, 8), Storage_Reference_Assy.xlsm!Table1[Design ID], 0), 4)

This gives me #N/A in all of my rows. If I manually take off the FC and 99 in the table and use formula

=INDEX(Storage_Reference_Assy.xlsm!Table1[#Data], MATCH([@[Design ID]], Storage_Reference_Assy.xlsm!Table1[Design ID], 0), 4)

It works no problem.
I have tried adding "" around the MID function but that did not work either.

If you have any suggestions or tips, let me know.
Thanks!
MK

 

[macattackpro]

Hi,

I believe the issue is that the MID function is returning a text response instead of a number which you are then trying to MATCH. Try adding -- before the MID so it looks like this:

=INDEX(Storage_Reference_Assy.xlsm!Table1[#Data], MATCH(--MID([@[Design ID]], 3, 8), Storage_Reference_Assy.xlsm!Table1[Design ID], 0), 4)

 

[mkron]

Hey,

That worked perfectly, thank you!
-mk

Hi,

I believe the issue is that the MID function is returning a text response instead of a number which you are then trying to MATCH. Try adding -- before the MID so it looks like this:

=INDEX(Storage_Reference_Assy.xlsm!Table1[#Data], MATCH(--MID([@[Design ID]], 3, 8), Storage_Reference_Assy.xlsm!Table1[Design ID], 0), 4)

 

[macattackpro]

Any time!