+ Reply to Thread
Results 1 to 4 of 4

Searching for multiple values in a "LOOKUP" function formula

  1. #1
    Registered User
    Join Date
    01-12-2019
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    8

    Searching for multiple values in a "LOOKUP" function formula

    Hi,

    I've developed a formula which looks for the LAST/FINAL value in an excel range that is equal to 1 and it works perfectly! I've used LOOKUP instead of MATCH as I am trying to find the final value, not the first. Here is my current working formula:

    =LOOKUP(2,1/([VALUE RANGE I AM SEARCHING IN]=1),[FINAL VALUE DISPLAYED IN CELL])

    Basically, if row B contains 20 numbers (and includes seven "1" values), it will search for the final value that displayed the number "1". The cell would then display the date above in row A from the last number "1" found.

    However I want to adjust this formula so I can find the final value if it is equal to 1, 2 or 3. I've tried the following but obviously it doesn't work:

    =LOOKUP(2,1/([VALUE RANGE I AM SEARCHING IN]=1,2,3),[FINAL VALUE DISPLAYED IN CELL])

    Thanks in advance for your help, it's a pretty complicated formula but works for exactly what I need
    Last edited by Candy Shakes; 01-13-2019 at 08:13 AM.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,830

    Re: Searching for multiple values in a "LOOKUP" function formula

    Hi,

    Two options:

    =LOOKUP(2,1/ISNUMBER(MATCH(A1:A20,{1,2,3},0)),B1:B20)

    =LOOKUP(2,1/MMULT(N(A1:A20={1,2,3}),{1;1;1}),B1:B20)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    01-12-2019
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    8

    Re: Searching for multiple values in a "LOOKUP" function formula

    You are a legend Thank you so much! Works 100% perfectly with all sets of data and no data / blanks (put in an IFERROR Statement to accompany this).

    I just swapped A1:A20 with B1:B20 (and vice versa) in your formula (for those who might come across this and find an error).

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,830

    Re: Searching for multiple values in a "LOOKUP" function formula

    Ah, yes - I got the ranges the wrong way round.

    Anyway, you're welcome!

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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