3 Reference Formulas Not Returning a Result

[Steve Belsch]

Hi,

Excel experts. I am stuck.

I have a formula that is attempting to reference three data points. But I am not getting the resulting I was needing. I want to reference 3 columns with the aggregate formula. For some reason I am not getting the results I need. Here is the formula. The cells that are references are BX2 and BZ2 and CA2. I expect that the data set gives a result of IF(AND) of the BX and CA and BZ columns. Fore example the columns in the rows in the data set that I am referencing doesn't produce the result of these references. It gives me more results than I was looking for.
I cant attach the workbook for some reason?. But here is the formula. And below are the references.

=IF(ROWS($A$3:A3)<=$BY$2,IF(AND($BZ$2=INDEX('Accrual & PO Data'!#REF!,AGGREGATE(15,3,('Accrual & PO Data'!#REF!=$BX$2)/('Accrual & PO Data'!#REF!=$BX$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($B$3:B3))),$BX$2=INDEX('Accrual & PO Data'!#REF!,AGGREGATE(15,3,('Accrual & PO Data'!#REF!=$BX$2)/('Accrual & PO Data'!#REF!=$BX$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($B$3:B3)))),INDEX('Accrual & PO Data'!#REF!,AGGREGATE(15,3,('Accrual & PO Data'!#REF!=$BX$2)/('Accrual & PO Data'!#REF!=$BX$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($B$3:B3))),""),"")

BW2 BX2 BZ2

Aptiom Grants

GR.ADJ

0

093

Anyy help would be greatly appreciated.

Thanks,
Steve

[Steve Belsch]

Any help is appreciated.

[Aussiebear]

Have you tried splitting the formula into parts to test each component?

[jazz2409]

Hi,

Excel experts. I am stuck.

I have a formula that is attempting to reference three data points. But I am not getting the resulting I was needing. I want to reference 3 columns with the aggregate formula. For some reason I am not getting the results I need. Here is the formula. The cells that are references are BX2 and BZ2 and CA2. I expect that the data set gives a result of IF(AND) of the BX and CA and BZ columns. Fore example the columns in the rows in the data set that I am referencing doesn't produce the result of these references. It gives me more results than I was looking for.
I cant attach the workbook for some reason?. But here is the formula. And below are the references.

=IF(ROWS($A$3:A3)<=$BY$2,IF(AND($BZ$2=INDEX('Accrual & PO Data'!#REF!,AGGREGATE(15,3,('Accrual & PO Data'!#REF!=$BX$2)/('Accrual & PO Data'!#REF!=$BX$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($B$3:B3))),$BX$2=INDEX('Accrual & PO Data'!#REF!,AGGREGATE(15,3,('Accrual & PO Data'!#REF!=$BX$2)/('Accrual & PO Data'!#REF!=$BX$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($B$3:B3)))),INDEX('Accrual & PO Data'!#REF!,AGGREGATE(15,3,('Accrual & PO Data'!#REF!=$BX$2)/('Accrual & PO Data'!#REF!=$BX$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($B$3:B3))),""),"")

BW2 BX2 BZ2

Aptiom Grants

GR.ADJ

0

093

Anyy help would be greatly appreciated.

Thanks,
Steve

Sorry I can't help but ask, did you notice the #REF!​? Maybe a column or something in the Accrual & PO Data sheet has been removed or moved.

[Steve Belsch]

Yes I have tested the parts of the formula. But maybe I am doing something wrong. How do I attach a document?

Here is the formula without #ref

=IF(ROWS($A$3:A3)<=$BY$2,IF(AND($BZ$2=INDEX('Accrual & PO Data'!$J$2:$J$1048576,AGGREGATE(15,3,('Accrual & PO Data'!$J$2:$J$1048576=$BZ$2)/('Accrual & PO Data'!$J$2:$J$1048576=$BZ$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($A$3:B3))),$BX$2=INDEX(LEFT('Accrual & PO Data'!$K$2:$K$1048576,2),AGGREGATE(15,3,(LEFT('Accrual & PO Data'!$K$2:$K$1048576,2)=$BX$2)/(LEFT('Accrual & PO Data'!$K$2:$K$1048576,2)=$BX$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($A$3:B3)))),INDEX('Accrual & PO Data'!A$2:A$1048576,AGGREGATE(15,3,(LEFT('Accrual & PO Data'!$K$2:$K$1048576,2)=$BX$2)/(LEFT('Accrual & PO Data'!$K$2:$K$1048576,2)=$BX$2)*ROW('Accrual & PO Data'!$I:$I),ROWS($A$3:B3))),""),"")

[jazz2409]

1. a.png
2. b.jpg

[Paul_Hossler]

or [Advanced] and click the paper clip


Capture.JPG

[Add Files] then [Choose File] then [Upload]

[Steve Belsch]

Any help is appreciated

[Aussiebear]

Bit hard to test when cells referred to contain no data

[Steve Belsch]

Here is the data.

[Aussiebear]

You are still making this difficult. If I go to cells BY2 and BZ2 there is no data in them. In trying to dismantle your formula I'm running into issues with the logic as wriiten by you. Can you please explain to us each step in the formula in common english?

.

[Bob Phillips]

Did you create this formula? If you did, the complexity suggests that you should know how to solve it yourself. If you didn't, I suggests that you go back to whoever supplied it in the first instance.

[paulked]

I'd look at the formula in BY2 1st, use helper column(s) to break it up. This seems to be the root but it's ref'd out in your attached workbooks.

[Steve Belsch]

I created this formula. However, for some reason it is returning more results than I wanted. The logic:

- Reference two cells BX and BY and if they are a match using IF(AND) then return the result from the "Accrual & PO Data" data set
- I used the aggregate formula because there are multiple instances of the BX and BY data that I expect to be returned. I can't use something simple like VLOOKUP because it won't return multiple results

Any other ideas would be appreciated. Any other formulas or maybe some VBA coding to make this work.

[Steve Belsch]

The BY formula is just a count if to prevent #NUM error message. But if there is a solution to the main formula then I could figure out how to fix the formula.

Thank you.