I have this table below. In another tab on the same worksheet, I want to extract all the values from "name" column, that match "status" as NotFound. My formula looks like this:
=INDEX('nameoftab'!A:A, MATCH("NotFound", 'nameoftab'!C:C, 0))
However the formula is only spitting the first value it finds which is Laura. How can I make it so I extract the remaining values in a simple list? I have looked everywhere and not a simple solution found, help please!
=INDEX('nameoftab'!B:B, AGGREGATE(14,6,ROW('nameoftab'!$C$2:$C$10)/('nameoftab'!$C$2:$C$10="NotFound"),ROW(A1))
That formula assumes the table in your picture is on worksheet named "nameoftab" and covers the range A1:C10.
Place that where you want the first name to appear and copy down as required. If you want to have it display blanks instead of an error message when you copy it beyond the list of names that match your criteria, wrap the whole thing in an IFERROR function:
=IFERROR(INDEX('nameoftab'!B:B, AGGREGATE(14,6,ROW('nameoftab'!$C$2:$C$10)/('nameoftab'!$C$2:$C$10="NotFound"),ROW(A1)),"")
Note, because equation 14 of AGGREGATE performs array like calculations, avoid using full column references within the array. Using full column reference for INDEX is fine as it is not within the AGGREGATE function.
To solve the issue you need Reverse Lookup Forula:
How it works:
A3:C11 in Sheet1.Sheet2, A1.Enter this Formula in Sheet2, A3 & fill it down.
=IF(Sheet1!C3=$A$1,VLOOKUP($A$1,CHOOSE({1,2},Sheet1!C$3:C$11,Sheet1!B3:B$11),2,0),"")
N.B.
In above formula, E1 is the lookup value. C3:C11 is the Column, Formula looks for the Lookup value in, and B3:B11 is the Column
Formula looks for the Target value.
With CHOOSE{1,2} 1 & 2 are Column Index considers Col C and B. And 2 sets the Column Index to get Name from Column B.
Edited:
If you want output like this, then you may use an Array (CSE) Formula shown below:
{=IFERROR(INDEX(Sheet1!$B$3:$B$11,SMALL(IF($A$1=Sheet1!$C$3:$C$11,ROW(Sheet1!$C$3:$C$11)- MIN(ROW(Sheet1!$C$3:$C$11))+1,""),ROW(A1))), "")}
Adjust cell references in the Formula as needed.
4 and the IF part returns Row no 3 and the IF with -MIN returns -1, means it sets the pointer to 1 Row to find the Match & extract the data, and so on. Small ends with ROW(A1) which sets the pointer to extract all matched data and start returning with 1st value and skip BLANKs. This is the theme behind the whole exercise. And difference between Formula 1 and 2, where Formula 1 also extracts the Value buts with BLANK Rows. ☺
Mar 1, 2019 at 5:16
IFERROR & INDEX,, from SMALL to ROW(A1) only, it return the Row position 1, 3, 7 of Laura, Albert & Maria. Which the Formula completes with INDEX($B$3:$B$11,Row Nu of Laura is 1) and extract the Name.
Mar 1, 2019 at 5:30
B:B or C:C or A:A , but my suggestion is use the required Range only and in case of CSE/Array Formula better use specific range with absolute reference like $A$1:$A$10, to minimize search area to SAVE time of execution, instead of the Whole Column reference. ☺
Mar 1, 2019 at 5:37