INDEX/MATCH or LOOKUP dilemma
INDEX/MATCH or LOOKUP dilemma
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: INDEX/MATCH or LOOKUP dilemma

  1. #1
    Board Regular
    Join Date
    Nov 2013
    Location
    Hampshire, UK
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default INDEX/MATCH or LOOKUP dilemma

     
    Hi guys and gals.

    So I am having trouble coming up with a solution to my problem.
    The scenario I have is an auto populating form based on one key cell.

    I select the serial number and the rest of the form fills out based on that selection, all of this work fine, until I get to the point that I need to lookup the previous instance of a Serial Number in a list.

    So I Select Key B11 and I need to lookup the previous instance of the associated unique serial xyz. (the serials are repeated as a history and struck out)
    The result that should be returned would be B8

    The Key is in column A:A
    The serial is in column B:B

    I have tried: =INDEX(A1:A30,MATCH(SerNum,B1:B30,0),0) - which returns the first instance
    I have tried: =IF(COUNTIF(B:B,SerNum),LOOKUP(2,1/(B:B=SerNum),A:A),""). - which returns the last instance.


    any help is appreciated.

    Kind Regards,
    Coops

    Key Serial
    B1 abc
    B2 xyz
    B3 456
    B4 abc
    B5 xyz
    B6 456
    B7 abc
    B8 xyz
    B9 abc
    B10 456
    B11 xyz

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    790
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX/MATCH or LOOKUP dilemma

    Hi,

    Try this:- Ctrl+Shift+Enter NOT just Enter

    Change the red value as needed...

    D1 =INDEX($A$1:$A$30,MAX(($B$1:$B$30=INDEX($B$1:$B$30,MATCH("B11",$A$1:$A$30,0)))*ROW($A$1:$A$30)*(ROW($A$1:$A$30) < SUM(((A1:A30="B11")*(ROW($A$1:$A$30)))))))


    A B C D
    1 Key Serial B8
    2 B1 abc
    3 B2 xyz
    4 B3 456
    5 B4 abc
    6 B5 xyz
    7 B6 456
    8 B7 abc
    9 B8 xyz
    10 B9 abc
    11 B10 456
    12 B11 xyz
    Last edited by admiral100; Dec 7th, 2017 at 03:52 PM.

  3. #3
    Board Regular
    Join Date
    Nov 2013
    Location
    Hampshire, UK
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INDEX/MATCH or LOOKUP dilemma

    Thanks Admiral,
    I will try and give it a go when I get back into the office tomorrow.

    Coops
    Last edited by cooper645; Dec 7th, 2017 at 04:22 PM.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  4. #4
    Board Regular
    Join Date
    Nov 2013
    Location
    Hampshire, UK
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INDEX/MATCH or LOOKUP dilemma

    This seems to return the result of the row below.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    Board Regular
    Join Date
    Jan 2015
    Posts
    790
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX/MATCH or LOOKUP dilemma

    Correct , the previous instance....Is that what you asked for?

  6. #6
    Board Regular
    Join Date
    Nov 2013
    Location
    Hampshire, UK
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INDEX/MATCH or LOOKUP dilemma

    Ah, I think I know where it went wrong, the ROW part uses all of tjem
    whereas I tested it with a range from A2, so I simply need to add -1 in there.

    I can then then adapt it to my actual worksheet where the data starts from ROW 17.

    im sure I can get this to work for me.

    Thanks for for taking the time and I will post back next week sometime when I figure it out or my brain dries.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  7. #7
    Board Regular
    Join Date
    Nov 2013
    Location
    Hampshire, UK
    Posts
    420
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INDEX/MATCH or LOOKUP dilemma

      
    Although I can get this working, when I try to implement it onto my actual sheet, I hit a snag, as the lookup data is on a different tab to the lookup value.

    Is is there a simple fix or would VBA be a better choice for this problem?

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

User Tag List

Tags for this Thread

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