# INDEX/MATCH Formula only spitting one value

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! • Do you mind use Power Query to get data? If possible, I can provide the detail steps. – Lee Feb 28 '19 at 9:20
• Turn on a filter based on "NotFound". Then just copy everything you want. – fixer1234 Feb 28 '19 at 9:28

``````=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:

• I'm assuming that you have data in Range `A3:C11` in `Sheet1`.
• Write the Criteria NotFound in `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))), "")}
``````
• Finish the Formula with Ctrl+Shift+Enter & Fill down.

Adjust cell references in the Formula as needed.

• Rajesh, thanks so much. I have some questions as I am not super well versed in Excel. Where is the ROW and MIN part coming from? Also I see we are locking the range by using \$ but can I only use a range (C:C, B:B) instead? Lastly I have tried putting the formula above and filling out, then I click Ctrl+Shift+Enter and nothing happens, the formula stays in the cell. – Michelle Carrizosa Feb 28 '19 at 18:04
• @MichelleCarrizosa, MIN part is returning Row no `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. ☺ – Rajesh S Mar 1 '19 at 5:16
• @MichelleCarrizosa, if you write the Formula without `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. – Rajesh S Mar 1 '19 at 5:30
• @MichelleCarrizosa, yes you can use `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. ☺ – Rajesh S Mar 1 '19 at 5:37