Index and Match for four columns.

[rgillson3]

I work with a report that requires a lot of filtering.

I've been using an Index-Match formula in two different columns to fill in filterable data.

Currently, Column K Indexes Col N, to Match Col O, and Col M Indexes Col Q to Match Col R.

How can I have one column Index do both?

I ran across the Index-Match-Match formula, but am not sure if it would work, or how to set it up.

 

[Greenbehindthecells]

I think I figured out how to use the xl2bb extension.

Index Test.xlsx
	I	J	K	L	M	N	O	P	Q	R
1						Mgr	20
2						Mgr	21		NON EMP	40
3	29.61					Mgr	22		NON EMP	41
4						Mgr	23		NON EMP	42
5	HAVE TO leave the "Yes" in this formula					Mgr	24		NON EMP	43
6						Mgr	25
7	Cell contains only a space: /NO					Mgr	26
8	No	NO ot	#N/A	#N/A	#N/A	Mgr	27
9						Mgr	28
10						Mgr	29
11	Blank?	OT?	Mgr	Mgr Name	To Go/Off-Site	Mgr	30
12
13	Yes	OT?	#N/A	#N/A	#N/A
14	Yes	OT?	#N/A	#N/A	#N/A
15	Yes	OT?	#N/A	#N/A	#N/A
16	Yes	OT?	#N/A	#N/A	#N/A
17	No	NO ot	#N/A	#N/A	#N/A
18	Yes	NO ot	#N/A	#N/A	#N/A
OT Report WE 08.20.2023_Fri (2)

Cell Formulas
Range	Formula
I3	I3	=MAX(C13:C438)
R3:R5,O2:O11	R3	=+R2+1
I8,I13:I18	I8	=IF(A8=" ","Yes","No")
J8,J13:J18	J8	=IF(C8>$G$2,"OT?","NO ot")
K8,K13:K18	K8	=INDEX($N$1:$N$11,MATCH(A8,$O$1:$O$11,0))
L8,L13:L18	L8	=IF(K8="Mgr",A8,"")
M8,M13:M18	M8	=INDEX($Q$1:$Q$11,MATCH(A8,$R$1:$R$11,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K13:K579	Expression	=K13="Mgr"	text	NO

Hello,

The xl2bb does not have values in cells (columns A-H are absent), so I had to put something in to try to work this. I cant see how using Match to a cell in column A in your indexes will return the result that you want (Column O's values = 20-30 Match to A17 and Column R values = 40-43 Match to A17) since both cannot be true with the example data you provided, I can only assume your real data will be able to Match A17 to the real values in these columns. If you want both Index & Match(es) to work consecutively, try the formula below for text with a space between both results. I cant check how this works due to the issue with no Columns A-H data in the mini sheet and both Match(es) to the same cell (A) cannot be True with the values in Columns O & R.

=(INDEX($N$1:$N$11,MATCH(A17,$O$1:$O$11,0))&" "&INDEX($Q$1:$Q$11,MATCH(A17,$R$1:$R$11,0)))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1														Mgr	20
2							3							Mgr	21		NON EMP	40
3									0					Mgr	22		NON EMP	41
4														Mgr	23		NON EMP	42
5									HAVE TO leave the "Yes" in this formula					Mgr	24		NON EMP	43
6														Mgr	25
7									Cell contains only a space: /NO					Mgr	26
8	30		8				7		No	OT?	Mgr	30	#N/A	Mgr	27
9														Mgr	28
10														Mgr	29
11									Blank?	OT?	Mgr	Mgr Name	To Go/Off-Site	Mgr	30
12
13	6		Data				Data		No	OT?	#N/A	#N/A	#N/A
14	29		Data				Data		No	OT?	Mgr	29	#N/A
15	28		Data				Data		No	OT?	Mgr	28	#N/A
16	27		Data				Data		No	OT?	Mgr	27	#N/A
17	26		Data				Data		No	OT?	Mgr	26	#N/A				#N/A
18	25		Data				Data		No	OT?	Mgr	25	#N/A
19
20
Sheet1

Cell Formulas
Range	Formula
I3	I3	=MAX(C13:C438)
R3:R5,O2:O11	R3	=+R2+1
I8,I13:I18	I8	=IF(A8=" ","Yes","No")
J8,J13:J18	J8	=IF(C8>$G$2,"OT?","NO ot")
K8,K13:K18	K8	=INDEX($N$1:$N$11,MATCH(A8,$O$1:$O$11,0))
L8,L13:L18	L8	=IF(K8="Mgr",A8,"")
M8,M13:M18	M8	=INDEX($Q$1:$Q$11,MATCH(A8,$R$1:$R$11,0))
Q17	Q17	=(INDEX($N$1:$N$11,MATCH(A17,$O$1:$O$11,0))&" "&INDEX($Q$1:$Q$11,MATCH(A17,$R$1:$R$11,0)))

 

[Alex Blakenburg]

Assuming the lookup value will be in either Column O or R, try this:

=XLOOKUP(A17, $O$1:$O$11, $N$1:$N$11, XLOOKUP(A17, $R$1:$R$11, $Q$1:$Q$11, ""))

Note: The Xlookup Format is below and the 2nd Xlookup is in the "if_not_found" position of the 1st Xlookup.
=XLOOKUP( lookup_value, lookup_array, return_array, if_not_found )

 

[rgillson3]

Hi - I'm sorry I haven't gotten back to you folks earlier. It's been a busy week.

I couldn't get the Index-Match formula to work. But, the XLOOPUP seems to do exactly what I want it to.

Thank you.

 

[Greenbehindthecells]

Hi - I'm sorry I haven't gotten back to you folks earlier. It's been a busy week.

I couldn't get the Index-Match formula to work. But, the XLOOPUP seems to do exactly what I want it to.

Thank you.

Great!