vlookup and MID in a array

DonAndress

Active Member
Joined
Sep 25, 2011
Messages
356
Hello.

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

Say, like below:
Code:
=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?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

JLeno

Board Regular
Joined
Mar 8, 2012
Messages
234
Try using Ctrl+Shift+Return, instead of just Return, when entering the function. Excel will treat the formula as an array formula :)
 

JLeno

Board Regular
Joined
Mar 8, 2012
Messages
234
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

Active Member
Joined
Sep 25, 2011
Messages
356

ADVERTISEMENT

Ok, thanks.

Now I have a code like below:

Code:
{=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

Board Regular
Joined
Mar 8, 2012
Messages
234
Code:
{=IF(ISERROR(VLOOKUP(N5,MID(RedbReds!$J$4:$J$30,33,11),1,FALSE)),"error","ok")}
 

DonAndress

Active Member
Joined
Sep 25, 2011
Messages
356

ADVERTISEMENT

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:

Code:
=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

Board Regular
Joined
Mar 8, 2012
Messages
234
Try this:

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

Cheers!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hello.

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

Say, like below:
Code:
=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

Board Regular
Joined
Mar 8, 2012
Messages
234
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,268
Messages
5,600,620
Members
414,394
Latest member
mahendar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top