Whats's up? Need some help working with a index match function that I am using to aggregate data from 4 separate columns and average them together as a total. The problem is that for half of the names I am searching, they have not data in the last 2 columns to be found and index/match simply returns N/A as it should because of a lack of data.
I need to find out how to have the function return something other than #N/A to me if the cell it is searching for has no data, it can return a 0 or simply just skip that look up in the total calculation.
Here is a copy of the formula I am using at the moment.
=AVERAGE(INDEX(C:C,MATCH(AO5,B:B,0)),INDEX(K:K,MATCH(AO5,J:J,0)),INDEX(S:S,MATCH(AO5,R:R,0)),INDEX(AA:AA,MATCH(AO5,Z:Z,0)))
For the players that have data in all 4 look up columns, this formula works exactly as intended but need to figure out how to get it to ignore the cells with no data for half of the list. Basically the 3rd and 4th index/match functions are the ones in question because everyone has data for the first 2 columns, it is only in columns, S & AA that I am having an issue because there is no reference for half of the field list and it returns N/A but i still need the average from the 1st arrays for these players.
Any help is much apprenticed, thanks community.
Bookmarks