Xlookup with multiple criteria

[zinah]

Hi,

I have two sheets that I need to map, I used xlookup based on employee ID, but what I need is to use employee ID and payment data:

not sure why I get error 91 when I tried to copy as mini table or sheet, but here's the table and formula I used, if anyone can help with adjusting the formula to get the payment amount based Employee ID and Scheduled Payment Date PPD End row:

Payment Date is 7/31/2023 =XLOOKUP(A13,H:H,K:K)
Payment Date is 8/15/2023 = =XLOOKUP(A14,H:H,L:L)
and so on

Column H

Column J

Column K

Column L

Column M

Column N

Column O

Column P

Column Q

PPD Start

7/15/2023​

8/1/2023​

8/16/2023​

9/1/2023​

9/16/2023​

10/1/2023​

10/16/2023​

Scheduled Payment Date

PPD end

7/31/2023​

8/15/2023​

8/31/2023​

9/15/2023​

9/30/2023​

10/15/2023​

10/31/2023​

Days in PPD

11​

11​

12​

11​

10​

10​

12​

Employee ID

Total days of severance

Payment amount

Payment amount

Payment amount

Payment amount

Payment amount

Payment amount

Payment amount

12345678​

25​

0​

2,773​

4,754​

2,377​

0​

0​

0​

23456789​

36​

0​

3,531​

7,062​

6,473​

4,119​

0​

0​

23456790​

31​

0​

6,473​

7,062​

4,708​

0​

0​

0​

Employee ID

Scheduled Payment Date

Amount

12345678​

7/31/2023​

0​

12345678​

8/15/2023​

2,773​

12345678​

8/31/2023​

4,754​

12345678​

9/15/2023​

2,377​

12345678​

9/30/2023​

0​

12345678​

10/15/2023​

12345678​

10/31/2023​

23456789​

7/31/2023​

0​

23456789​

8/15/2023​

3,531​

23456789​

8/31/2023​

23456789​

9/15/2023​

23456789​

9/30/2023​

23456789​

10/15/2023​

23456789​

10/31/2023​

 

[SkaFreaks]

Xlookup isn't great when working with multiple criteria, especially if you are trying to find something using both rows and columns. What you should use instead is index/match/match.

Utilizing your data above, the formula would look something like =INDEX($H$3:$Q$9,MATCH(A15,H$3:H$9,0),MATCH(B15,$H$4:$Q$4,0))

Here is a great article explaining the formula in detail

INDEX MATCH MATCH in Excel (How to do 2-dimension lookup)

This post covers one of the most powerful lookup functions combinations available in Excel, INDEX MATCH MATCH. Use it for 2-dimension lookups.

exceloffthegrid.com

 

[AhoyNC]

Try:
Change ranges to match your data

Book1
	A	B	C				G	H	I	J	K	L	M	N	O	P	Q	R
1								Column H		Column J	Column K	Column L	Column M	Column N	Column O	Column P	Column Q
2											PPD Start	7/15/2023	8/1/2023	8/16/2023	9/1/2023	9/16/2023	10/1/2023	10/16/2023
3										Scheduled Payment Date	PPD end	7/31/2023	8/15/2023	8/31/2023	9/15/2023	9/30/2023	10/15/2023	10/31/2023
4											Days in PPD	11	11	12	11	10	10	12
5								Employee ID			Total days of severance	Payment amount	Payment amount	Payment amount	Payment amount	Payment amount	Payment amount	Payment amount
6								12345678			25	0	2773	4754	2377	0	0	0
7								23456789			36	0	3531	7062	6473	4119	0	0
8								23456790			31	0	6473	7062	4708	0	0	0
12	Employee ID	Scheduled Payment Date	Amount
13	12345678	7/31/2023	0
14	12345678	8/15/2023	2773
15	12345678	8/31/2023	4754
16	12345678	9/15/2023	2377
17	12345678	9/30/2023	0
18	12345678	10/15/2023	0
19	12345678	10/31/2023	0
20	23456789	7/31/2023	0
21	23456789	8/15/2023	3531
22	23456789	8/31/2023	7062
23	23456789	9/15/2023	6473
24	23456789	9/30/2023	4119
25	23456789	10/15/2023	0
26	23456789	10/31/2023	0
Sheet1

Cell Formulas
Range	Formula
C13:C26	C13	=INDEX($L$6:$R$8,MATCH($A13,$H$6:$H$8,0),MATCH($B13,$L$3:$R$3,0))