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]

Hello,

Can you use the xl2bb extension and show where your data is, and your current formula so I can try to assist?

Thanks

 

[rgillson3]

Hi,

I need a refresher on how to use the xl2bb extension.

Rob

 

[Joe4]

Hi,

I need a refresher on how to use the xl2bb extension.

Rob

See: XL2BB - Excel Range to BBCode

 

[rgillson3]

Thank you. I'll have to work on it later. I'm getting a bit behind today.

 

[rgillson3]

I haven't been able to xl2bb extension to work, probably due to user err.

Is there any way to "next" two index and match formulas together in the same cell/column?

I'm currently using =INDEX($N$1:$N$11,MATCH(A17,$O$1:$O$11,0)) and =INDEX($Q$1:$Q$11,MATCH(A17,$R$1:$R$11,0)).

I'd like to pull all my matches in a single column.

 

[Greenbehindthecells]

Are the results of your index match formulas numeric values that can be added together? Or text?

 

[rgillson3]

The results are text.

 

[rgillson3]

Hello - I haven't seen another reply to this.

 

[rgillson3]

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