INDEX & MATCH in excel

Question

I have this table in excel

I want to create a new column where if have a total score let's say of 92 I do an index/match on the table and get it. I tried this

=INDEX('Risk Assessment Matrix '!I24:I28,1,1,MATCH(111,'Risk Assessment Matrix '!J24:J28,0)) 

but not working.

Any help please?

Thanks, Ilias

Answer 1

An alternate option is the use the new XLOOKUP function.

Although it would require changing the minimum value range from 0 to -9999. An out of range value for non-numeric values of 9999 would also need to be added.

It has the advantage of being easier to read compared to INDEX and MATCH.

=XLOOKUP($D2,$B$2:$B$6,$A$2:$A$6,"Not Found",-1)

The formula looks up the value against a single list of minimum values.

The match_mode (last parameter) is set to -1. This means perform an exact match and if nothing is found return the next smaller item.

Answer 2

Two methods shown:

Pay attention to the order.

Answer 3

Your INDEX/MATCH looks a bit odd. You wouldn't normally have the "1,1," in there.

Try this:

=INDEX('Risk Assessment Matrix '!I24:I28, MATCH(111,'Risk Assessment Matrix '!J24:J28,0))

This should return "High" (assuming your first column in the table is "I".

Answer 4

Reorder the list so the third column is ascending not descending. and change the >=0 to just 0

Then use:

=INDEX('Risk Assessment Matrix '!I24:I28,MATCH(111,'Risk Assessment Matrix '!K24:K28)) 

Answer 5

Assuming a value given is not above 140, you don't even need a 3rd column. Try:

Formula in D2:

=INDEX(A1:A5,MATCH(D1,B1:B5,-1))

If D1 happens to be above 140 an error is returned. You can catch that by nesting the above in =IFERROR(<TheAbove>,"No Match") for example.