XLookup with named ranges and InDirect

[Jazz Engineer]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	64,400	36,400	36,700	35,000	13,000	8,500	10,200	23,600	90,700	7,500	17,500	8,500
3
4
5		Oct	7,500	=XLOOKUP(B5,A1:L1,A2:L2)
6		Feb	#VALUE!
Example 2

Cell Formulas
Range	Formula
D5	D5	=FORMULATEXT(C5)
C5	C5	=XLOOKUP(B5,A1:L1,A2:L2)
C6	C6	=XLOOKUP(B6,Months_RA,INDIRECT(B6))

Named Ranges
Name	Refers To	Cells
Apr	='Example 2'!$D$2	C5
Aug	='Example 2'!$H$2	C5
Dec	='Example 2'!$L$2	C5
Feb	='Example 2'!$B$2	C5
Jan	='Example 2'!$A$2	C5
Jul	='Example 2'!$G$2	C5
Jun	='Example 2'!$F$2	C5
Mar	='Example 2'!$C$2	C5
May	='Example 2'!$E$2	C5
Months_RA	='Example 2'!$A$1:$L$1	C5:C6
Nov	='Example 2'!$K$2	C5
Oct	='Example 2'!$J$2	C5
Sep	='Example 2'!$I$2	C5

 

[Jazz Engineer]

I got so concerned with XL2BB that I forgot to post my question. In the sample above, the formula in C6 does not work, but I can't figure out why. Please help. The Indirect function would appear to use the right references in the right position of the formula, but something isn't working.

Thanks,
Steve/Jazz Engineer

 

[Automatrix]

What should the XLOOKUP be if you didn't use named ranges?

 

[Oaktree]

The reason C6 is returning a #VALUE error isn't because of the INDIRECT function... it's because your arrays are different sizes.

In your example, Months_RA is an array that is 1 row x 12 columns... but each of your months' named ranges are single cells.

Rewriting C6's formula for the named ranges' references, C6's formula is the equivalent of =XLOOKUP(B6,A1:L1,B2), which gives #VALUE error because the 2nd and 3rd parameters are different sizes.

Maybe something got lost in translation as you tried to generalize your actual use into the sample you provided?

 

[Jazz Engineer]

The reason C6 is returning a #VALUE error isn't because of the INDIRECT function... it's because your arrays are different sizes.

In your example, Months_RA is an array that is 1 row x 12 columns... but each of your months' named ranges are single cells.

Rewriting C6's formula for the named ranges' references, C6's formula is the equivalent of =XLOOKUP(B6,A1:L1,B2), which gives #VALUE error because the 2nd and 3rd parameters are different sizes.

Maybe something got lost in translation as you tried to generalize your actual use into the sample you provided?

OakTree,

Thanks for your response. This is not the first time I've made this mistake. But I missed the solution you seemed to provide. Did you? Also, is there any easy fix to this formula? I will try to make the 3rd parameter A2:L2.

Steve

 

[Oaktree]

Honestly, my recommendation is just to use the formula you already have in C5 and remove the named ranges entirely. C5 is more transparent than looking through 13 named ranges would be...

If there are other reasons for using the named ranges, I'd just have one for the labels (e.g. Months_RA = A1:L1) and one for the values (e.g. Values_RA = A2:L2).

Then, you could use =XLOOKUP(B6, Months_RA,Values_RA)

 

[Jazz Engineer]

OK. I think I resolved it with an HLookUp. I thought I was doing what you recommend but I couldn't figure it out. I know this is a very simple example, but I've been working with pretty large data sets and my curiosity got to me.

Thanks, Oaktree,

Steve