0

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!

Original table

  • 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
0
=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.

| improve this answer | |
0

To solve the issue you need Reverse Lookup Forula:

enter image description here

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:

enter image description here

{=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.

| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.