INDEX MATCH with multiple criteria over a range

**greg1233** · 07-16-2019, 09:17 AM

Hi everyone,

I'm trying to use an INDEX match array formula to find data between 3 columns.

This formula works fine:
=INDEX(A9:I512,MATCH(1,(A9:A600=I2)*(B9:B600=I3),0),9)

Which finds the first entry when the first two columns are true. However the third column is numbers and I want to find the row between two numbers (so changing the '0' in the match formula to 1 or -1).

This formula just returns the same row every single time (regardless of what I search for):
=INDEX(A9:I512,MATCH(1,(A9:A512=I2)*(B9:B512=I3)*(C9:C512=I4),1),9)

Can anyone help?

**Richard Buttrey** · 07-16-2019, 09:24 AM

Welcome to the forum,

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

**greg1233** · 07-16-2019, 09:48 AM

Hi, I've attaché this workbook.

**Richard Buttrey** · 07-16-2019, 01:36 PM

I'd be inclined to use a helper column E with E2 copied down

Formula:

Please Login or Register  to view this content.

Then your formula is

Formula:

Please Login or Register  to view this content.

**greg1233** · 07-16-2019, 02:35 PM

This has worked absloutely perfect. Thank you Richard.

I assumed a helper column wouldn't work due to mixing a string with numbers but alas (and thankfully) I am wrong!

INDEX MATCH with multiple criteria over a range

LinkBack

Thread Tools

Rate This Thread

Display

INDEX MATCH with multiple criteria over a range

Re: INDEX MATCH with multiple criteria over a range

Re: INDEX MATCH with multiple criteria over a range

Re: INDEX MATCH with multiple criteria over a range

Re: INDEX MATCH with multiple criteria over a range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index Match with multiple criteria and date criteria

[SOLVED] Index match with multiple criteria, with one criteria being greater than a certain date

[SOLVED] Index Match with multiple criteria including date range

Need help in Index, Match usage to match multiple criteria in sum function

Index/Match to Match entries on multiple criteria

[SOLVED] Index Match with Multiple Criteria Using Same Criteria Column

Formula needed for partial string match as part of index match with multiple criteria

Bookmarks

Bookmarks

Posting Permissions