Forum Discussion
INDEX function help
I'm going to try to explain this well haha.
In the image below, I want all POs (Column V) that have an "11" beside them (Column U) to be listed in Column X.
The formula I'm using is below.
In column Z (for those POs with an "18" beside them), I tried a simpler formula, but it has spaces between POs which won't work for the list's intended use.
All cells are formatted to "general" and Calculations are set to "automatic".
The full formula I'm using:
=IF(ISERROR(INDEX($U$3:$V$38,SMALL(IF($U$3:$U$38=$X$2,ROW($U$3:$U$38)),ROW(1:1))-2,2)),"",INDEX($U$3:$V$38,SMALL(IF($U$3:$U$38=$X$2,ROW($U$3:$U$38)),ROW(1:1))-2,2))
I've also done the array-entered version and it does not work.
The thing is, this formula works PERFECTLY in another book that I use for a different company. I don't understand this at all.
The simpler formula I used in Column Z is:
=IF(U3=18,V3,"")
However, those spaces cause issue with another formula elsewhere that relies on the list.
Microsoft 365, Excel Version 2406 (Build 17726.20160 Click to Run)
If with old style that could be
=IFERROR( INDEX( $V$3:$V$21, AGGREGATE( 15,6, (ROW($U$3:$U$21)-ROW($V$2))/($U$3:$U$21=X$2), ROW()-ROW(X$2) ) ), "" )Please see attached.
4 Replies
- SergeiBaklanDiamond Contributor
If with old style that could be
=IFERROR( INDEX( $V$3:$V$21, AGGREGATE( 15,6, (ROW($U$3:$U$21)-ROW($V$2))/($U$3:$U$21=X$2), ROW()-ROW(X$2) ) ), "" )Please see attached.
- ColemanR2450Copper Contributor
- SergeiBaklanDiamond Contributor
ColemanR2450 , you are welcome
- Rodrigo_Iron Contributor