XLOOKUP two-way, use "if not found" for both ways

[aapdonBK]

Hi,
I'm trying to look up a department A14 (in columns) and account A13 (in rows). Sometimes, the department won't exist. Other times the account may not exist.

This formula works if the account does not exist and returns a zero. Great!
=+XLOOKUP($A14,$A$2:$A$8,XLOOKUP($B14,$B$1:$D$1,$B$2:$D$8),0)
But, if the department does not exist, I get an "#N/A".

So, I tried adding another "if not found" in bold here:
=+XLOOKUP($A15,$A$2:$A$8,XLOOKUP($B15,$B$1:$D$1,$B$2:$D$8,0),0)
But, I got "#VALUE!" instead of a zero.

I cannot figure out how to have both the column and the row XLOOKUP in this nested formula use "if not found" criteria. The data changes every quarter, and I don't want to have to add fake data or mess with the formula each time. I need it to return 0 if either or both are not found.

Any ideas on using XLOOKUP here?

Thanks!

 

[Alex Blakenburg]

You are going to need to use IFERROR. If you don't need to return a different result based on which one is erroring out then just use that and don't worry about the XLookup version.

=IFERROR(XLOOKUP($A14,$A$2:$A$8,XLOOKUP($B14,$B$1:$D$1,$B$2:$D$8)),0)

 

[djclements]

The nested (inner) XLOOKUP function is returning a single column of values, which is used as the return_array argument of the outer XLOOKUP function. If you want to utilize the [if_not_found] argument of the inner XLOOKUP function, it will also need to be a single column of values that is the same height as the lookup_array. In this particular example, since you only want to return a 0 when no match is found, you can simply specify an empty range somewhere off to the right of your data table, e.g. $E$2:$E$8 or $XFD$2:$XFD$8.

=XLOOKUP(A13,$A$2:$A$8,XLOOKUP(B13,$B$1:$D$1,$B$2:$D$8,$XFD$2:$XFD$8),0)

 

[Peter_SSs]

Welcome to the MrExcel board!

Is there a particular need to use XLOOKUP at all?

24 12 03.xlsm
	A	B	C	D
1	Account	Marketing	IT	Admin
2	1000	40	50	60
3	1100	1000	10	80
4	1200	45	55	65
5	1300	1003	13	83
6	1400	50	60	70
7	1500	1006	16	86
8	1600	55	65	75
9
10
11
12
13	1200	Marketing	45
14	1650	IT	0
15	1600	Overhead	0
16	1600	Overhead	0
Sheet2 (2)

Cell Formulas
Range	Formula
C13:C16	C13	=IFNA(INDEX(B$2:D$8,XMATCH(A13,A$2:A$8),XMATCH(B13,B$1:D$1)),0)

 

[aapdonBK]

The nested (inner) XLOOKUP function is returning a single column of values, which is used as the return_array argument of the outer XLOOKUP function. If you want to utilize the [if_not_found] argument of the inner XLOOKUP function, it will also need to be a single column of values that is the same height as the lookup_array. In this particular example, since you only want to return a 0 when no match is found, you can simply specify an empty range somewhere off to the right of your data table, e.g. $E$2:$E$8 or $XFD$2:$XFD$8.

=XLOOKUP(A13,$A$2:$A$8,XLOOKUP(B13,$B$1:$D$1,$B$2:$D$8,$XFD$2:$XFD$8),0)

Thanks for the explaining "single column of values that is the same height".

 

[aapdonBK]

You are going to need to use IFERROR. If you don't need to return a different result based on which one is erroring out then just use that and don't worry about the XLookup version.

=IFERROR(XLOOKUP($A14,$A$2:$A$8,XLOOKUP($B14,$B$1:$D$1,$B$2:$D$8)),0)

Thanks for this easy solution.

 

[aapdonBK]

Welcome to the MrExcel board!

Is there a particular need to use XLOOKUP at all?

24 12 03.xlsm
	A	B	C	D
1	Account	Marketing	IT	Admin
2	1000	40	50	60
3	1100	1000	10	80
4	1200	45	55	65
5	1300	1003	13	83
6	1400	50	60	70
7	1500	1006	16	86
8	1600	55	65	75
9
10
11
12
13	1200	Marketing	45
14	1650	IT	0
15	1600	Overhead	0
16	1600	Overhead	0
Sheet2 (2)

Cell Formulas
Range	Formula
C13:C16	C13	=IFNA(INDEX(B$2:D$8,XMATCH(A13,A$2:A$8),XMATCH(B13,B$1:D$1)),0)

Thanks for this working alternative. I'm just learning more about XLOOKUP.

 

[Alex Blakenburg]

We appreciate the feedback. Glad we could help.