vlookup and MID in a array

[DonAndress]

Hello.

Is it possible to create a formula that would use MID in an array of VLOOKUP?

Say, like below:

=VLOOKUP(N4,MID(RedbReds!J4:J30,33,11),1,FALSE)

When the array is like a normal range, vlookup returns what I wanted but when I add MID (and I know exactly starting point and no of characters) it returns #VALUE!.

Is it the only way to create MID function in a separate range and then define VLOOKUP to search in that new range?

 

[Aladin Akyurek]

Good point, didn't think of that. That solution is definitely less complicated and will often work.

However, it could (depending on the data) lead to different results:
Lookup: ABC
value 1: xxABCxxxxxxDEFxxx
value 2: xxABCxxxxxxABCxxx

The 'MID' formula (with start:12 and length:3) will select value 2
The "*" formula will select value 1.

VLOOKUP will pick out the first "ABC" match.

If the second or all matches of "ABC" are required, a different set up is required.