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
CodePudding user response:
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.
CodePudding user response:
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))
CodePudding user response:
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".
CodePudding user response:
Two methods shown:
Pay attention to the order.
CodePudding user response:
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.