Page 1 of 2 12 LastLast
Results 1 to 10 of 11

vlookup and MID in a array

This is a discussion on vlookup and MID in a array within the Excel Questions forums, part of the Question Forums category; Hello. Is it possible to create a formula that would use MID in an array of VLOOKUP? Say, like below: ...

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    259

    Default vlookup and MID in a array

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2012
    Posts
    234

    Default Re: vlookup and MID in a array

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

  3. #3
    Board Regular
    Join Date
    Sep 2011
    Posts
    259

    Default Re: vlookup and MID in a array

    Good God that works!
    Thanks a lot.

  4. #4
    Board Regular
    Join Date
    Mar 2012
    Posts
    234

    Default Re: vlookup and MID in a array

    Quote Originally Posted by DonAndress View Post
    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!

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Posts
    259

    Default Re: vlookup and MID in a array

    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?

  6. #6
    Board Regular
    Join Date
    Mar 2012
    Posts
    234

    Default Re: vlookup and MID in a array

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

  7. #7
    Board Regular
    Join Date
    Sep 2011
    Posts
    259

    Default Re: vlookup and MID in a array

    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).

  8. #8
    Board Regular
    Join Date
    Mar 2012
    Posts
    234

    Default Re: vlookup and MID in a array

    Try this:

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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,363

    Default Re: vlookup and MID in a array

    Quote Originally Posted by DonAndress View Post
    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?
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    Board Regular
    Join Date
    Mar 2012
    Posts
    234

    Default Re: vlookup and MID in a array

    Quote Originally Posted by Aladin Akyurek View Post
    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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com