Formula to pull data based on cell value

**rp1783** · 04-14-2015, 08:37 PM

This is a very noob quesiton, and I'm a little disappointed at myself for not knowing how to accomplish this.

I have 2 sheets. On sheet 1 I have numerous columns. One of the columns contains phone numbers, another column contains the name that the phone number is assigned to, or it says "Available". What I'm trying to do is have all of the numbers that show Available pulled into the second sheet. The second sheet will have 2 columns. 1 with the phone number, and the other column will say available for all of numbers (since they will all be available). I thought about using vlookup, but I don't have a unique identifier since not all phone numbers will be on the second sheet. Any suggestions?

**rcm** · 04-14-2015, 08:48 PM

Based on your description...
adjust the column name to search for the name (assumed "B")
adjust the names of the input sheet and the output sheet
adjust the phone number column (assumed 1)

Please Login or Register  to view this content.

**Tony Valko** · 04-14-2015, 08:53 PM

See if you can adapt this:

Lookup with multiple instances of the lookup value

**rp1783** · 04-14-2015, 09:09 PM

Originally Posted by rcm

Based on your description...
adjust the column name to search for the name (assumed "B")
adjust the names of the input sheet and the output sheet
adjust the phone number column (assumed 1)

When running this I get a syntax error. I don't know that I explained myself very well. I have attached an example of the results I'm looking for.Example_04142015.xlsx

**Tony Valko** · 04-14-2015, 09:29 PM

This array formula** entered in A1 on Sheet2:

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!C$1:C$6="Available",ROW(Sheet1!C$1:C$6)),ROWS(A$1:A1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

**rp1783** · 04-14-2015, 09:45 PM

For some reason, this is just returning the header of my column in sheet1. My example again was not as clear as it should have been. I copied the formula down, but still have blanks. I should have also mentioned that my working document has approximately 85000 rows.

EDIT: I did attempt to adjust the formula to look in all of column C.