# Searching for multiple values in a "LOOKUP" function formula

1. ## 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   Register To Reply

2. ## 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  Register To Reply

3. ## 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).  Register To Reply

4. ## 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  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### 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