Share via

I am using index / match with a large function embedded. The formula was working when i was using small but now i have changed, it is returning 0 rather than the correct answer. can anyone help?

Anonymous
2023-05-12T07:07:12+00:00

=INDEX($U:$U,MATCH(SMALL(AH:AH,$B105),AH:AH,0))

this function was working and returning the correct answer

=INDEX($U:$U,MATCH(LARGE(AH:AH,$B105),AH:AH,0))

however this is returning 0 rather than the correct answer.

U:U is the column i want it to spit out

AH:AH is the column i want it to rank

row B has the list of 1-10 for ranking

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2023-05-12T07:16:42+00:00

    Empty cells are calculated as zero, that's reason for the result.

    Format the data as table and refer the table column as reference.

    Andreas.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-05-12T07:13:30+00:00

    Don't use entire column as reference, it sometimes will cause some unexpected result.

    Try formula like this one.

    =INDEX($U2:$U20000,MATCH(LARGE(AH2:AH20000,$B105),AH2:AH20000,0))

    Was this answer helpful?

    0 comments