Formula for multiple variable row find

[aqibi2000]

I have been playing with INDEX Match and Vlookup, however am struggling to solve my problem, I normally end up using 10+ multiple column filters to reach my results.

Made an example below where I input 3 variables and wish to find a Row which matches all the criteria, in the example the results would be H.

Would really appreciate the help

Data1.xlsx
	A	B	C	D	E	F	G	H
1
2	Row Found	H		Row	Var1:	Var2:	Var3:
3	Input Var1:	1		A	0	2	4
4	Input Var2:	3		B	1	2	4
5	Input Var3:	5		C	0	3	4
6				D	1	3	4
7				E	0	2	5
8				F	1	2	5
9				G	0	3	5
10				H	1	3	5
11
12
Sheet1

Thank you

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[aqibi2000]

Wish to achieve this with using only a single formula, I can solve this by using variables and if statements:

Data1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Row Found	H		Row	Var1:	Var2:	Var3:		if Var1	if Var2	if Var3	AND(Var1&Var2&Var3)
3	Input Var1:	1		A	0	2	4		0	0	0	FALSE
4	Input Var2:	3		B	1	2	4		1	0	0	FALSE
5	Input Var3:	5		C	0	3	4		0	1	0	FALSE
6				D	1	3	4		1	1	0	FALSE
7				E	0	2	5		0	0	1	FALSE
8				F	1	2	5		1	0	1	FALSE
9				G	0	3	5		0	1	1	FALSE
10				H	1	3	5		1	1	1	TRUE
11
12											True =	H
13
14
Sheet1

Cell Formulas
Range	Formula
I3:I10	I3	=IF($B$3=E3,1,0)
J3:J10	J3	=IF($B$4=F3,1,0)
K3:K10	K3	=IF($B$5=G3,1,0)
L3:L10	L3	=AND(I3:K3)
L12	L12	=INDEX(D3:D10,MATCH(TRUE,L3:L10,0))

 

[aqibi2000]

With to do this without using an additional column:

Data1.xlsx
	A	B	C	D	E	F	G	H
1
2	Row Found	H		Row	Var1:	Var2:	Var3:
3	Input Var1:	1		A	0	2	4	FALSE
4	Input Var2:	3		B	1	2	4	FALSE
5	Input Var3:	5		C	0	3	4	FALSE
6				D	1	3	4	FALSE
7				E	0	2	5	FALSE
8				F	1	2	5	FALSE
9				G	0	3	5	FALSE
10				H	1	3	5	TRUE
11
12								H
Sheet1

Cell Formulas
Range	Formula
H3:H10	H3	=IF($E$3:$E$10=$B$3,IF($F$3:$F$10=$B$4,IF($G$3:$G$10=$B$5,TRUE,FALSE)))
H12	H12	=INDEX(D3:D10,MATCH(TRUE,H3:H10,0))

 

[Fluff]

Maybe you did not see post#2?
Please update you profile to show what version of Excel you are using.

 

[aqibi2000]

Maybe you did not see post#2?
Please update you profile to show what version of Excel you are using.

I have done so.

 

[Fluff]

Thanks for that.
A couple of options

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	Row Found	H	H	Row	Var1:	Var2:	Var3:
3	Input Var1:	1		A	0	2	4
4	Input Var2:	3		B	1	2	4
5	Input Var3:	5		C	0	3	4
6				D	1	3	4
7				E	0	2	5
8				F	1	2	5
9				G	0	3	5
10				H	1	3	5
11
Lists

Cell Formulas
Range	Formula
B2	B2	=INDEX(D3:D10,AGGREGATE(15,6,(ROW(D3:D10)-ROW(D3)+1)/(E3:E10=B3)/(F3:F10=B4)/(G3:G10=B5),1))
C2	C2	=INDEX(D3:D10,MATCH(1,(E3:E10=B3)*(F3:F10=B4)*(G3:G10=B5),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[aqibi2000]

Thanks for that.
A couple of options

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	Row Found	H	H	Row	Var1:	Var2:	Var3:
3	Input Var1:	1		A	0	2	4
4	Input Var2:	3		B	1	2	4
5	Input Var3:	5		C	0	3	4
6				D	1	3	4
7				E	0	2	5
8				F	1	2	5
9				G	0	3	5
10				H	1	3	5
11
Lists

Cell Formulas
Range	Formula
B2	B2	=INDEX(D3:D10,AGGREGATE(15,6,(ROW(D3:D10)-ROW(D3)+1)/(E3:E10=B3)/(F3:F10=B4)/(G3:G10=B5),1))
C2	C2	=INDEX(D3:D10,MATCH(1,(E3:E10=B3)*(F3:F10=B4)*(G3:G10=B5),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you very much, this is perfect!

I was wondering if I had more than 1 match is there anyway to display this too?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	Row Found		F	Row	Var1:	Var2:	Var3:
3	Input Var1:	1	H	A	0	2	4
4	Input Var2:	3		B	1	2	4
5	Input Var3:	5		C	0	3	4
6				D	1	3	4
7				E	0	2	5
8				F	1	3	5
9				G	0	3	5
10				H	1	3	5
Lists

Cell Formulas
Range	Formula
C2:C4	C2	=IFERROR(INDEX($D$3:$D$10,AGGREGATE(15,6,(ROW($D$3:$D$10)-ROW($D$3)+1)/($E$3:$E$10=$B$3)/($F$3:$F$10=$B$4)/($G$3:$G$10=$B$5),ROWS(C$2:C2))),"")

 

[aqibi2000]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	Row Found		F	Row	Var1:	Var2:	Var3:
3	Input Var1:	1	H	A	0	2	4
4	Input Var2:	3		B	1	2	4
5	Input Var3:	5		C	0	3	4
6				D	1	3	4
7				E	0	2	5
8				F	1	3	5
9				G	0	3	5
10				H	1	3	5
Lists

Cell Formulas
Range	Formula
C2:C4	C2	=IFERROR(INDEX($D$3:$D$10,AGGREGATE(15,6,(ROW($D$3:$D$10)-ROW($D$3)+1)/($E$3:$E$10=$B$3)/($F$3:$F$10=$B$4)/($G$3:$G$10=$B$5),ROWS(C$2:C2))),"")

Amazing thank you! ?