XLookup with named ranges and InDirect

Jazz Engineer

New Member
Joined
Feb 1, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDEFGHIJKL
1JanFebMarAprMayJunJulAugSepOctNovDec
264,40036,40036,70035,00013,0008,50010,20023,60090,7007,50017,5008,500
3
4
5Oct7,500=XLOOKUP(B5,A1:L1,A2:L2)
6Feb#VALUE!
Example 2
Cell Formulas
RangeFormula
D5D5=FORMULATEXT(C5)
C5C5=XLOOKUP(B5,A1:L1,A2:L2)
C6C6=XLOOKUP(B6,Months_RA,INDIRECT(B6))
Named Ranges
NameRefers ToCells
Apr='Example 2'!$D$2C5
Aug='Example 2'!$H$2C5
Dec='Example 2'!$L$2C5
Feb='Example 2'!$B$2C5
Jan='Example 2'!$A$2C5
Jul='Example 2'!$G$2C5
Jun='Example 2'!$F$2C5
Mar='Example 2'!$C$2C5
May='Example 2'!$E$2C5
Months_RA='Example 2'!$A$1:$L$1C5:C6
Nov='Example 2'!$K$2C5
Oct='Example 2'!$J$2C5
Sep='Example 2'!$I$2C5
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top