Forum Discussion
Solved
How can I use index formular only on certain cells?
At the moment I have the following formular
=INDEX(F4:R4,MATCH(MAX(F6:R6),F6:R6,0))
The issue I am having is I only want some of the cells included, so for example.
F4, H4, J4, L4, N4, P4, R4, T4
When I replace F4:R4 with this arrangment it fails. I am new to this and I am unsure what I am missing.
- if the values in F6:R6 are unique then:
=INDEX(F4:R4,MATCH(MAX(F6, H6, J6, L6, N6, P6, R6, T6),F6:R6,0))
since all those cells are even columns and assuming the values in F6:R6 are positive values then you could also use:
=INDEX(F4:R4,MATCH(MAX(F6:R6)*ISEVEN(F6:R6),F6:R6,0))
If you have excel 365 then you can use LET and FILTER and XLOOKUP
1 Reply
- if the values in F6:R6 are unique then:
=INDEX(F4:R4,MATCH(MAX(F6, H6, J6, L6, N6, P6, R6, T6),F6:R6,0))
since all those cells are even columns and assuming the values in F6:R6 are positive values then you could also use:
=INDEX(F4:R4,MATCH(MAX(F6:R6)*ISEVEN(F6:R6),F6:R6,0))
If you have excel 365 then you can use LET and FILTER and XLOOKUP