I'm truly stuck....

corppunishment11

Board Regular
Joined
Dec 7, 2018
Messages
51
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!!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
The formula is looking at C2 on the sheet containing the formula
 
Upvote 0
On sheet1


Excel 2013/2016
BC
2St AlbansE05004800
3St AlbansE05004797
4HertsmereE05004747
5HertsmereE05004802
6HertsmereE05004794
7St AlbansE05004787
8St AlbansE05004793
9Welwyn HatfieldE05011063
10Welwyn HatfieldE05004792
11St AlbansE05004791
12St AlbansE05004801
Sheet1


and on sheet 2


Excel 2013/2016
CDE
2St AlbansE05004800
3E05004797
4E05004787
5E05004793
6E05004791
7E05004801
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top