Vlookup with indirect returning 0, when value is present

ChrisTag1

New Member
Joined
Jan 13, 2020
Messages
11
Office Version
2016
Platform
Windows
Hey everyone,

Looking for some help again. I have a formula..... =IFERROR(IF($G$8="Primary",VLOOKUP(I$8,INDIRECT("PrimaryDailyRange"),2,0),IF($G$8="Secondary",VLOOKUP(I$8,INDIRECT("SecondaryDailyRange"),2,0))),0),
that works exactly how I need it to in one worksheet. The next worksheet is almost exactly the same as the first, except I am getting the data from a different named range. I am trying to use this same formula with the new named range, but it returns 0 instead of the data in the referenced cell. =IFERROR(IF($G$8="Primary",VLOOKUP(U$8,INDIRECT("PrimaryWeeklyRange"),2,0),IF($G$8="Secondary",VLOOKUP(U$8,INDIRECT("SecondaryWeeklyRange"),2,0))),0)
What am I doing wrong? I don't understand why it works for one named range and not for the other.

Any ideas?

Thanks all.
 

Attachments

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,334
Office Version
2019, 2016, 2013
Platform
Windows
Don't know but your primaryweekly range seems to have a space as the first part of the data, in which case it maybe the IFEEROR is triggered which is why the zero. If you made 0 into a word you would see its the error trap
 

FatBoyClam

Board Regular
Joined
Jan 8, 2020
Messages
183
Office Version
365
Platform
Windows
Looks to me like the 0 it's returning is likely to be the result of the IFERROR function.

Try taking the IFERROR out, or using the inline evaluation of the formula to check for errors.
My guess would be that the date you're looking up doesn't actually exist in the lookup table (the years are not displayed, so can't be certain one way or the other)
 

FatBoyClam

Board Regular
Joined
Jan 8, 2020
Messages
183
Office Version
365
Platform
Windows
Also - as far as I see the use of Indirect here is redundant (you can just use the range name directly in the vlookup formula).

However, you could simplify your formula (and make it more efficient) by leveraging the naming convention and the INDIRECT function;

=IFERROR(VLOOKUP(U$8,INDIRECT($G$8&"WeeklyRange"),2,0),0)

This concatenates the value from G8 (Primary or Secondary) with "WeeklyRange" (common between the two range names), and feeds it into the INDIRECT function to get a reference to the relevant range.

This should be a more efficient option, where your formula is doing two cell comparisons (the test part of the IF statements) and two VLOOKUPS (Excel evaluates every element of a formula), where the above does one INDIRECT and one VLOOKUP.
 

ChrisTag1

New Member
Joined
Jan 13, 2020
Messages
11
Office Version
2016
Platform
Windows
FatBoyClam,
I redid the formula without the IFERROR, and the error I am getting is #NA. I thought it was the dates too, but the dates are all the same in every worksheet. So in the first worksheet, where the formula works, it is looking up the same dates as in the second worksheet. So I am really at a loss as to why I can't get my formula to return a value that I can plainly see is in the cell.
 

ChrisTag1

New Member
Joined
Jan 13, 2020
Messages
11
Office Version
2016
Platform
Windows
FatBoyClam,
You were correct about the date. In the 2 tables being referenced in my formula, the date was a text instead of a date. (ie "10/28/19" instead of 43766). I have been so used to the answers to my problems being complicated that I completely overlooked the obvious.

Cheers
 

FatBoyClam

Board Regular
Joined
Jan 8, 2020
Messages
183
Office Version
365
Platform
Windows
Glad you got to the answer Chris (y)
Hope the shortened formula works for you, if you try it out.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,880
Messages
5,465,238
Members
406,419
Latest member
ldp124

This Week's Hot Topics

Top