INDEX and MATCH - return value even though not all criteria are met

**varbergt** · 03-25-2024, 05:50 AM

Hi,
I'm using below formula to make lookup with multiple criteria.

Formula:

Please Login or Register  to view this content.

As you can see from below picture I have 4 criteria that are being matched on a sheet called "Proxy".
In this example location is "London". However, on the "Proxy" sheet most of the times location is BLANK. Therefore, the formula returns nothing. Is there a way to make the formula return a result even though location has a value but the reference sheet does not?

Screenshot 2024-03-25 104521.png

Sample attached.

Thanks
Thomas

**AliGW** · 03-25-2024, 05:52 AM

Try this:

=IFNA(INDEX(Proxy!$H$1:$H$2319,MATCH(1,(E4=Proxy!$D$1:$D$2319)*(F4=Proxy!$C$1:$C$2319)*("Product Development"=Proxy!$B$1:$B$2319)*(G4=Proxy!$E$1:$E$2319)*(IF(Proxy!$F$1:$F$2319="",1,H4=Proxy!$F$1:$F$2319)),0)),"")

**rorya** · 03-25-2024, 09:14 AM

Since you have 365, you could also use something like:

Formula:

Please Login or Register  to view this content.

If you know there will only ever be one match (or none) you could omit the TAKE function.

**DJunqueira** · 03-25-2024, 10:10 AM

Could also use:

Formula:

Please Login or Register  to view this content.

**varbergt** · 03-26-2024, 03:22 AM

This did the job! Thanks AliGW!

**AliGW** · 03-26-2024, 03:43 AM

Glad to have helped.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

INDEX and MATCH - return value even though not all criteria are met

LinkBack

Thread Tools

Rate This Thread

Display

INDEX and MATCH - return value even though not all criteria are met

Re: INDEX and MATCH - return value even though not all criteria are met

Re: INDEX and MATCH - return value even though not all criteria are met

Re: INDEX and MATCH - return value even though not all criteria are met

Re: INDEX and MATCH - return value even though not all criteria are met

Re: INDEX and MATCH - return value even though not all criteria are met

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index match with 2 criteria but want a different cell as return value

Index/Match Help to return value based on 2 criteria

[SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match

[SOLVED] INDEX MATCH trying to return a value between two criteria.

[SOLVED] Using Index Match with a Criteria to return a minimum

Index Match using two criteria to return on of criteria values

Need some help please. Trying to return rows that fit my criteria via INDEX/MATCH.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions