Index / Match

USAFRetired20

Looking for some guidance.

Goal: If "KPI Availability %" @ row is equal to or greater than "Annual Score % Availability" row 10 then it will look at the "Previous Contract Year Event" @ row and match it with the column header 0 , 2,3,4,5,6,7,8 (individual columns). Then it will look down the matched column where it matched the row and return the value in the cell. If it doesn't match row then then it will look at all the preceding rows for a match.

Here is what I am using, and obviously it isn't looking for >, <, or =

=INDEX([0]1:[8]10, MATCH([KPI Availability %]@row , [Annual Score % Availability]1:[Annual Score % Availability]10))

I also tried this:

jjg279

You need to add a "reference row" at the top that is equal to your column names - Index/Match doesn't use the headers.

Something like =IF("KPI Availability %"@row >= "Annual Score % Availability"@row, INDEX(0@row:8@row, 1, MATCH("Previous Contract Year Event" @ row, REFERENCE_ROW0:REFERENCE_ROW8, 0))

Alternatively, if you could swap your zeros for ones you could just use that as your column reference instead of the Match section. Index is like saying to down "number" of rows and over "number" of columns to my cell, so your row is your row and if you just gave it your 0-8 number it would go that many over.