Extract from string and use result for vlookup - doesn't work?

[kingofcamden]

Hi all,

So I have this string in cell C9 (WR.NEA.541.00.2022.06.15.I3) and want to extract the 541 value from it.
Formula I used is this:

=TRIM(CLEAN(MID(SUBSTITUTE(C9,".",REPT(" ",100)),200,100)))

I am now trying to use the output value (541) for a vlookup on another table so am combining the two formulae:

=VLOOKUP(TRIM(CLEAN(MID(SUBSTITUTE(C9,".",REPT(" ",100)),200,100))),Lookups!$B$6:$F$204,4,FALSE)

(Note: Lookups!$B$6:$F$204 contains this number - triple-checked - and the 4th column contains the output value - also triple checked).

This formula gives me a #N/A?!

If I evaluate the formula, everything looks fine - it goes all the way to "VLOOKUP("541",Lookups!$B$6:$F$204,4,FALSE)", but then returns the #N/A. As if the value would not exist in range Lookups!$B$6:$F$204, when it 1000% does.

Here are some things I've already tried:
1. As REPT is rendering a Text format output, I thought Excel may not find a match as one cell is formatted as 'Text' and the other as 'General', but that's not it. Both values are formatted as 'General'
2. I've also tried different formulae without REPT to extract the 541 value...same result:

=LEFT(TEXTJOIN("",TRUE,IFERROR(--MID(C9,ROW(INDIRECT("1:"&LEN(C9))),1),"")),LEN(TEXTJOIN("",TRUE,IFERROR(--MID(C9,ROW(INDIRECT("1:"&LEN(C9))),1),"")))-11)

=CLEAN(TRIM(LEFT(MID(C9,FIND("#",SUBSTITUTE(C9,".","#",2))+1,255),LEN(MID(C9,FIND("#",SUBSTITUTE(C9,".","#",2))+1,255))-22)))

3. I've also tried INDEX/MATCH for the lookup...also did not work:

=INDEX(Lookups!$E$6:$E$204,MATCH(TRIM(CLEAN(MID(SUBSTITUTE(C9,".",REPT(" ",100)),200,100))), Lookups!$B$6:$B$204,0))

=INDEX(Lookups!$E$6:$E$204,MATCH(CLEAN(TRIM(LEFT(MID(C9,FIND("#",SUBSTITUTE(C9,".","#",2))+1,255),LEN(MID(C9,FIND("#",SUBSTITUTE(C9,".","#",2))+1,255))-22))), Lookups!$B$6:$B$204,0))

#N/A errors all around...

This is driving me nuts haha...Spent hours trying to fix this lookup, but no luck.

Any thoughts?

Appreciate the help!

[6StringJazzer]

Your first formula is giving you a string and the data you are searching is probably numbers. Try this revision to your second formula:

=VLOOKUP(VALUE(TRIM(CLEAN(MID(SUBSTITUTE(C9,".",REPT(" ",100)),200,100)))),Lookups!$B$6:$F$204,4,FALSE)

[TMS]

I suspect it is simply because you are using MID to extract some information. This will give a Text return, regardless of the content extracted. So, if the search table contains numeric data, it will not match ... "123" <> 123 as far as Excel is concerned.

Try just adding zero at the end of your initial formula:

=TRIM(CLEAN(MID(SUBSTITUTE(C9,".",REPT(" ",100)),200,100)))+0

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[kingofcamden]

OMG! For real?! Haha...the +0 did the job! Thanks TMS!

6StringJazzer, good suggestion - had tried that too, but somehow did not work right.

Happy I can finally close this chapter!

[TMS]

You're welcome. Thanks for the rep.