I'm truly stuck....

[corppunishment11]

I am trying to write a formula that will look at a constant data point (C2) and return all values related to C2. In other words, list all items from a different spreadsheet that refer to John Doe

Example:

=IFERROR(VLOOKUP($C$2,'sheet1'l$B$2:$C$153,2,FALSE),"")

For the first cell, it returns what I want.

C2: John Doe E2 shows, "1,000,000" perfect.

However, E3 through E150 also show, "1,000,000".

How do I get it to keep looking at C2 but return all of the other values?

Thanks!!

 

[Fluff]

Try
=IFERROR(INDEX(Sheet1!$C$2:$C$28,SMALL(IF(Sheet1!$B$2:$B$28=$C$2,ROW(Sheet1!$B$2:$B$28)-ROW(Sheet1!$B$2)+1),ROWS($1:1))),"")

This needs to be confirmed with Ctrl Shift enter not just Enter

 

[steve the fish]

You need to enter this into a formula using CTRL-SHIFT-ENTER

=IFERROR(INDEX(Sheet1!$C$2:$C$153,SMALL(IF(Sheet1!$B$2:$B$153=$C$2,ROW($B$2:$B$153)-ROW($B$2)+1),ROWS($A$1:A1))),"")

Drag down until you start getting blank results.

 

[corppunishment11]

Thank you both.

to be sure I understand, is this saying that the C2 is still my constant data point, "John Doe" and that everything found on the other sheet, "sheet1" will look for John Doe and bring back all info related to him?

Also, I don't understand the, You need to enter this into a formula using CTRL-SHIFT-ENTER

Thank you again.

 

[Fluff]

The answer to your first question is Yes.
To the second copy and paste the formula into E2, then double click the cell & press Ctrl Shift & Enter at the same time.
If done correctly you will see that the formula is now wrapped in { }
Then drag the formula down

 

[Kenneth Hobson]

For a multiple find, one can use Chip Pearson's FindAll(). http://www.cpearson.com/Excel/findall.aspx

Once the range is found, you can use Offset() to find the values needed.

 

[corppunishment11]

Oh, that worked for the {}.

I want to be sue I'm learning how to fish here. Looking at the formula and trying to understand how it reads, it looks like it's saying that C2 is on sheet 1 and the other data is also on sheet 1.

The constant data point is on sheet 2 and pulling the other data where there is a column of different names, is sheet 1. That has all the names "john doe, frank smith, etc" in B2 and the dollars I need is in column C as laid out in my original formula.

Again just making sure I'm learning this new formula not just taking from the board.

Thanks again.

 

[Fluff]

The formula is looking at C2 on the sheet containing the formula

 

[corppunishment11]

Okay. Not sure why I cannot get this to work. I'll keep messing with it.

Thank you everyone!

 

[Fluff]

On sheet1

Excel 2013/2016
	B	C
2	St Albans	E05004800
3	St Albans	E05004797
4	Hertsmere	E05004747
5	Hertsmere	E05004802
6	Hertsmere	E05004794
7	St Albans	E05004787
8	St Albans	E05004793
9	Welwyn Hatfield	E05011063
10	Welwyn Hatfield	E05004792
11	St Albans	E05004791
12	St Albans	E05004801
Sheet1

and on sheet 2

Excel 2013/2016
	C	D	E
2	St Albans		E05004800
3			E05004797
4			E05004787
5			E05004793
6			E05004791
7			E05004801
Sheet2

Cell Formulas
Range	Formula
E2	{=IFERROR(INDEX(Sheet1!$C$2:$C$28,SMALL(IF(Sheet1!$B$2:$B$28=$C$2,ROW(Sheet1!$B$2:$B$28)-ROW(Sheet1!$B$2)+1),ROWS($1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.