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!

 

[ExceLoki]

try this:

Book1
	A	B	C	D	E	F	G	H	I
2		sunrise	sunrise			consent	consent
3	dates	fund i	vs bank	refill	transaction id n*	fund ii	vs bank	refill	transaction id n*
4	9/28/2022	1,500,000	1,500,020	cap call 1	fundi1	1,600,000	1,600,020	refill from	fundii1
5	10/4/2022	-2,500,000	-2,500,020	cap call 2	fundi2	-3,500,000	-3,500,020	cap call 1	fundii2
6
7
8
9
10
11
12
13
14			fundi1	fund i	1500000
Sheet1

Cell Formulas
Range	Formula
E14	E14	=INDEX(A4:I5, IFERROR(MATCH(C14,E4:E5, 0),MATCH(C14,I4:I5, 0)), MATCH(D14,A3:I3, 0))

 

[smetu]

Thank you very much! It works very well.
I was not precise enough though in my example as the formula you wrote works only if you have 2 funds and the truth is I have 35 in my file.
I prepare an example more accurate.
Do you think you can help me as well find a proper formula? Without moving anything in the table above.

 

[ExceLoki]

in your first exampl you wre looking for an amount, in the second one a date. just want to clarify what it is you're looking for...
are the fund names: fundi1, fundi2, fundii1, fundii2, etc... ever repeated?

 

[smetu]

in your first exampl you wre looking for an amount, in the second one a date. just want to clarify what it is you're looking for...
are the fund names: fundi1, fundi2, fundii1, fundii2, etc... ever repeated?

The Fund names are very different from one another unfortunately.
I'm looking for the date AND the amount, yes. But you can do a formula to return only the date, that would be already wonderful.

 

[ExceLoki]

try this

=INDIRECT(ADDRESS(IFERROR(IF(E12,4),5),1))

 

[smetu]

It doesn't work...
What do 4,5,1 represent? And E12 refers to an empty cell...

 

[ExceLoki]

4 & 5 refer to which row the value in c14 is found in, and the 1 is the column where the date is.
sorry, i had something in e12 that i didnt include in the previous msg, try this:

=INDIRECT(ADDRESS(IFERROR(IF(MATCH(C14,4:4,0),4),5),1))

 

[smetu]

Sorry, your formula won't work... I have 1500 rows with Transaction ID's in reality.

 

[ExceLoki]

ok, i'm working on another solution that will still work for you