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]

In that case there is nothing wrong with the formula.
Is the value in the dropdown exactly the same as the data in col B of the Pending sheet?
What does the formula return?

 

[corppunishment11]

Hi. I agree the formula is fine. something else must be wrong.

Yes, I do have it matching perfectly.

If I have john doe selected on sheet p it's returning a value assigned to a N/A and if i make it Jack doe, or any other name on the list it returns the same value for all and that coincidentally is related to john doe.

 

[Fluff]

Would you be willing to upload you file to a share site such as OneDrive, DropBox,GoogleDrive & post a link to the thread?

 

[corppunishment11]

I would love to but the information is proprietary. Hence, John Doe.

It's okay. I'll keep playing with it to see what's wrong. You've been more than gracious.

Would screen shots work if I black out info?

 

[Fluff]

In this instance a image would probably not really help.

A couple of things to check
Is calculation set to Automatic?
Is your dropdown a data validation dropdown, or is it a form or ActiveX combobox?

 

[corppunishment11]

The dropdown is a data validation drop. Calc is set to auto.

Is it the drop that's the issue?

 

[Fluff]

No, a DV dropdown is fine.
Are the formula & the DV on the same sheet?
if so in an empty cell on that sheet enter =B5=Pending!B11
where B11 has the same value as the dropdown, what does the formula return?

 

[corppunishment11]

Hi. It came back with True.

 

[Fluff]

In case I'm afraid I don't know what's going wrong.

 

[corppunishment11]

Here is my exact formula on Pending sheet (Sheet 1)
{=IFERROR(INDEX('Pending Data'!$C$2:$C$1364,SMALL(IF('Pending Data'!$B$2:$B$1364=B5,ROW('Pending Data'!$B$2:$B$28)-ROW('Pending Data'!$B$2)+1),ROWS($1:1))),"")}

On Pending Data sheet ( Sheet 2)
B2 = The name I need to use to validate with my drop down on Sheet 1
C2 = The dollar amount that I need returned if B2 has the name I choose in my drop down

I also cannot find what I'm doing wrong.