+ Reply to Thread
Results 1 to 13 of 13

Vlookup - Return 2nd or 3th or other pre-defined correspondence

  1. #1
    Registered User
    Join Date
    12-14-2015
    Location
    Évora, Portugal
    MS-Off Ver
    MS Excel 2107
    Posts
    11

    Vlookup - Return 2nd or 3th or other pre-defined correspondence

    Hi All,

    This my first question in this forum, so if i'm doing something wrong I'm sorry.

    So the question is, It's possible that the vlookup formula in stead of return only/allways the first correspondence found, we configure it to return the 2nd or the 3th or other pre-defined correspondence found in the column? it's possible do this with the Vlookup function?

    If it's not possible do this with the vlookup function, there is another function or way that can? how?

    Thanks in advance for your support!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    Like this...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    A
    67
    X
    2
    86
    2
    S
    23
    3
    X
    58
    4
    D
    1
    5
    X
    86
    6
    D
    23
    7
    S
    68
    8
    A
    50
    9
    X
    46
    10
    A
    88
    11
    ------
    ------
    ------
    ------
    ------
    ------


    This array formula** entered in F1:

    =INDEX(B:B,SMALL(IF(A1:A10=D1,ROW(A1:A10)),E1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    You may have to replace the commas with semi-colons.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    12-14-2015
    Location
    Évora, Portugal
    MS-Off Ver
    MS Excel 2107
    Posts
    11

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    Thanks Tony,

    I confess that I never tryed or use an array formula.

    Capturar.JPG

  4. #4
    Registered User
    Join Date
    12-14-2015
    Location
    Évora, Portugal
    MS-Off Ver
    MS Excel 2107
    Posts
    11

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    What I really need is that the function/formula returns all te codes founded for the name "John". Everytime "John" appears in Column A, the formula should return the following.

    ex:
    1st "John" -> return "Monday_5"
    2nd "john" -> return "Tuesday_6"

    and so on....

    it will the array works for this answear needed?

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    OK, based on your screencap what result do you expect?

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    Try this...

    F2 = John

    This array formula** entered in G2:

    =IFERROR(INDEX(D:D,SMALL(IF(A$2:A$13=F$2,ROW(A$2:A$13)),ROWS(G$2:G2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

  7. #7
    Registered User
    Join Date
    12-14-2015
    Location
    Évora, Portugal
    MS-Off Ver
    MS Excel 2107
    Posts
    11

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    What I expect from my function, is list like this:
    Capturar2.JPG

  8. #8
    Registered User
    Join Date
    12-14-2015
    Location
    Évora, Portugal
    MS-Off Ver
    MS Excel 2107
    Posts
    11

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    Hi Tony

    With your sugested function, what is returned is an error. Please see attached:
    Capturar3.JPG

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    I assume #NOME? means #NAME? in English?

    What version of Excel did you use?

  10. #10
    Registered User
    Join Date
    12-14-2015
    Location
    Évora, Portugal
    MS-Off Ver
    MS Excel 2107
    Posts
    11

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    So, to clarify, this is what I want to get when I wright the function for each name or cell that contains a name:

    Capturar4.JPG

  11. #11
    Registered User
    Join Date
    12-14-2015
    Location
    Évora, Portugal
    MS-Off Ver
    MS Excel 2107
    Posts
    11

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    Hi Tony,

    Yes NOME mens NAME is portuguese.

    I use Excel 2013

  12. #12
    Registered User
    Join Date
    12-14-2015
    Location
    Évora, Portugal
    MS-Off Ver
    MS Excel 2107
    Posts
    11

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    Sorry,

    My fault. The function you sent me was in english but my excel is in portuguese. I'd change the function to portuguese, and what get now is an blank cell??!!
    Capturar5.JPG

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Vlookup - Return 2nd or 3th or other pre-defined correspondence

    Here's a small sample file created in Excel 2013 that demonstrates this.

    Don't forget, the formulas are array formulas!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combine VLOOKUP and SUMIF to return sum of user-defined ranges in a table
    By sciondraconis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-27-2014, 03:27 AM
  2. checck and display the correspondence output
    By gurum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2013, 08:00 AM
  3. Return top 3 values and their correspondence
    By RandomP in forum Excel General
    Replies: 4
    Last Post: 11-11-2011, 11:13 AM
  4. Application Defined or Object Defined Error, Command Button and Vlookup Function
    By stevedomer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-14-2011, 11:20 PM
  5. Looking for a correspondence in VBA
    By seoer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2011, 07:51 AM
  6. How to find max value with correspondence text
    By nasg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-25-2010, 05:55 AM
  7. Tracking correspondence
    By rhone81 in forum Excel General
    Replies: 0
    Last Post: 05-20-2008, 08:19 PM
  8. [SOLVED] vlookup, add parameter, on error return user defined value
    By jims2994 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-07-2006, 10:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1