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]

.

 

[ExceLoki]

this should do the trick

find a value based on 2 criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		sunrise	sunrise			consent	consent
3	dates	fund i	vs bank	refill	transaction id n*	fund ii	vs bank	refill	transaction id n*	fund iii	vs bank	refill	transaction id n*	fund iv	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	1,600,000	1,600,020	refill from	fundiii1	1,600,000	1,600,020	refill from	fundiv1
5	10/4/2022	-2,500,000	-2,500,020	cap call 2	fundi2	-3,500,000	-3,500,020	cap call 1	fundii2	-3,500,000	-3,500,020	cap call 1	fundiii2	-3,500,000	-3,500,020	cap call 1	fundiv2
6	3/23/2023	test0			fundi3	test1			fundii3	test2			fundiii3	test3			985
7
8
9
10
11
12
13
14			fundii1	dates	9/28/2022
final

Cell Formulas
Range	Formula
E14	E14	=OFFSET(INDIRECT(ADDRESS(3,MATCH(D14,3:3,0))),(MATCH(TRUE,MMULT(IFERROR(FIND(C14,FILTER(A3:Q6,A3:Q3="transaction id n*")),0),TRANSPOSE(SEQUENCE(1,COUNTIF(3:3,"transaction id n*"),1,0)))>0,0))-1,0)

 

[smetu]

My table is on another sheet called DATA, so I've tried to modify your formula as such:

=OFFSET(INDIRECT(ADDRESS(3,MATCH(D14,Data!3:3,0))),(MATCH(TRUE,MMULT(IFERROR(FIND(C14,FILTER(Data!A3:Q6,Data!A3:Q3="transaction id n*")),0),TRANSPOSE(SEQUENCE(1,COUNTIF(Data!3:3,E3),1,0)))>0,0))-1,0) But it doesn't work.

 

[smetu]

My table is on another sheet called DATA, so I've tried to modify your formula as such:

=OFFSET(INDIRECT(ADDRESS(3,MATCH(D14,Data!3:3,0))),(MATCH(TRUE,MMULT(IFERROR(FIND(C14,FILTER(Data!A3:Q6,Data!A3:Q3="transaction id n*")),0),TRANSPOSE(SEQUENCE(1,COUNTIF(Data!3:3,E3),1,0)))>0,0))-1,0) But it doesn't work.

 

[smetu]

And I don't know if it's important for you to know but on my file on my sheet "Data", the row is not 3 but 22.

 

[smetu]

=OFFSET(INDIRECT(ADDRESS(22,MATCH(A22,'[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!22:22,0))),(MATCH(TRUE,MMULT(IFERROR(FIND(G23,FILTER('[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!A22:TQ3000,'[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!A22:TQ22=G22)),0),TRANSPOSE(SEQUENCE(1,COUNTIF('[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!22:22,G22),1,0)))>0,0))-1,0)

(The real name of my sheet is "Private Equity Fund Dashboard.xlsx]DDowns & Distri" and here is my formula that I've tried to do according to yours. But I must be doing something wrong as it doesn't work....)

 

[smetu]

I put it here again so it's clearer for you:

=OFFSET(INDIRECT(ADDRESS(22,MATCH("Dates",'[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!22:22,0))),(MATCH(TRUE,MMULT(IFERROR(FIND("10DI1",FILTER('[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!A22:TQ3000,'[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!A22:TQ22="Transaction ID n°")),0),TRANSPOSE(SEQUENCE(1,COUNTIF('[Private Equity Fund Dashboard.xlsx]DDowns & Distri'!22:22,"Transaction ID n°"),1,0)))>0,0))-1,0)

 

[ExceLoki]

so it's on a different row? on a different worksheet? in a different workbook???

 

[smetu]

so it's on a different row? on a different worksheet? in a different workbook???

Yes

 

[ExceLoki]

can you give me the top-left most cell you're referencing and the bottom-right most cell??