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?

 

[JLeno]

Try using Ctrl+Shift+Return, instead of just Return, when entering the function. Excel will treat the formula as an array formula

 

[DonAndress]

Good God that works!
Thanks a lot.

 

[JLeno]

Good God that works!
Thanks a lot.

You're welcome!

Pleae note that this will not work if you select multiple columns, and the return column is not equal to the lookup column (Excel will also use the MID formula on the other columns in the array).

Cheers!

 

[DonAndress]

Ok, thanks.

Now I have a code like below:

{=IF(N5=VLOOKUP(N5,MID(RedbReds!$J$4:$J$30,33,11),1,FALSE),"ok","error")}

But instead if returning "error" it returns "#N/A".

How should I rewrite it to get "error" if "IF" returns false?

 

[JLeno]

{=IF(ISERROR(VLOOKUP(N5,MID(RedbReds!$J$4:$J$30,33,11),1,FALSE)),"error","ok")}

 

[DonAndress]

Great, thank you.

Now, is it possible use MID like you presented above but get a value from a column not in the array (and what's more important previous column then VLOOKUP value)?

I mean Ihave:

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

But now I need to get a value from the row where VLOOKUP returned true but from column E (while the VLOOKUP value was in column J).
</pre>

 

[JLeno]

Try this:

{=INDEX(RedbReds!E4:E30,MATCH(N4,MID(RedbReds!J4:J30,33,11),0))}

Cheers!

 

[Aladin Akyurek]

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?

Why not just:

=VLOOKUP("*"&N4&"*",RedbReds!J4:J30,1,0)

instead of creating an array reference?

 

[JLeno]

Why not just:

=VLOOKUP("*"&N4&"*",RedbReds!J4:J30,1,0)

instead of creating an array reference?

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.