How to find a value based on 2 criteria: one horizontal and one vertical BUT which is not always in the same column.

[smetu]

Is there a way to find a formula in the green box based on the grey and yellow boxes.
The fact that the transaction ID's of Fund I and Fund II are not in the same columns prevents me to find a solution to this problem.....
It's been a head scratching for me for weeeeks!

 

[smetu]

that's not quite what i meant. if there are more or less cells with "Transaction ID" as the value in row 22 than there are in a22:tq22 then that will throw off the function.

try putting these in somewhere:

=FILTER('[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!$A$22:$Q$25,'[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!$A$22:$TQ$22="transaction id n*"))

&

=COUNTIF('[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!$22:$22,"transaction id n*")

the number from the second one should match the number of columns returned by the first one

What do you mean by "try to put this somewhere"? Where should I put it?

 

[smetu]

What do you mean by "try to put this somewhere"? Where should I put it?

I feel like we are getting ourselves lost in translation here.... I don't follow you anymore, sorry.

 

[ExceLoki]

put it in anywhere, maybe a new sheet that you can delete after. this is just to test and see if the numbers match.
i'm trying to trouble shoot a viable solution that i cannot see the sheet for

 

[smetu]

put it in anywhere, maybe a new sheet that you can delete after. this is just to test and see if the numbers match.
i'm trying to trouble shoot a viable solution that i cannot see the sheet for

Got it. Done it.
So your 1st formula returns: #VALUE!
And your 2nd formula returns: 57

 

[smetu]

Is it on purpose that the ranges in your 1st formula are A$22:Q$25 and then A$22:TQ$22?

 

[ExceLoki]

no, the first part should be the entire table searching data in which i think is a22:tc2752, the second part is right
=FILTER('[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!$A$22:$TQ$2752,'[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!$A$22:$TQ$22="transaction id n*"))

 

[smetu]

no, the first part should be the entire table searching data in which i think is a22:tc2752, the second part is right
=FILTER('[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!$A$22:$TQ$2752,'[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!$A$22:$TQ$22="transaction id n*"))

Now I get: #SPILL!

 

[smetu]

I have an idea.
If you can tell me a formula that will write D2 when I ask where is Monday, and C2 when I ask where is Soccer like in the table here.

Then I think I might have a solution to my problem.

Do you know a formula that can do that?

 

[ExceLoki]

Now I get: #SPILL!

so you'd need to put it somewhere that has a lot of room. it cant show everything that the formula is asking it to return due to other values in the way

 

[smetu]