Solved

A different HLOOKUP problem #REF!

Posted on 2013-11-22
19
250 Views
Last Modified: 2013-11-24
Folks,
I had to move a table in my worksheet that is used in a HLOOKUP function. The worksheet is attached. I am now getting a different error that I cannot see: #REF!
Please open attachment to see what I mean and help me to understand what is wrong.
Thanks
HLookup-problem.xlsm
0
Comment
Question by:Frank Freese
  • 6
  • 5
  • 5
  • +1
19 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 167 total points
ID: 39669726
=IFERROR(IF($B$2="","",IF(HLOOKUP($B$2,$C$2:$F$5,ROW()-7,FALSE)=0,"",HLOOKUP($B$2,$C$2:$F$5,ROW()-7,FALSE))),"")
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39669727
try:

=IF($B$2="","",IFERROR(IF(HLOOKUP($B$2,$C$2:$F$5,ROWS($A$9:$A9)+1,FALSE)=0,"",HLOOKUP($B$2,$C$2:$F$5,ROWS($A$9:$A9)+1,FALSE)),""))
0
 

Author Comment

by:Frank Freese
ID: 39669772
The reason I selected your soultion was it was returning the dates I was expecting. The other solution did not.
Thanks to all.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39669788
Actually, I just compared my solution with ssaqibh's solution, and both give same results....
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39669790
Added a date row and other changes.  Attachement included.  Here's the formula for the second row of your important dates (as an example.)

=IF(ISERROR(HLOOKUP($B$2,$C$2:$F$5,ROW($B$2),FALSE))=TRUE,"",IF(HLOOKUP($B$2,$C$2:$F$5,ROW($B$2)+(A16-1),FALSE)=0,"",HLOOKUP($B$2,$C$2:$F$5,ROW($B$2)+A16-1,FALSE)))

="if the whole formula is invalide then nothing", "if the formula delivers zero then nothing", "deliver results."

The ROW($b$2) gives the row number of your month headers.  The 16 gives the row number of your results.  By using these variables you can move stuff around without breaking it.

lvbarnes
Copy-of-HLookup-problem.xlsm
0
 

Author Comment

by:Frank Freese
ID: 39669874
Folks,
The best solution of the three was lvbarnes. I revisited the other two solutions and determined that the row() - was wrong for both, giving me dates such as 01/12/2010 for the 12th month in addition to the correct date of 12/3/2010. Once I adjusted for the row count I got the information I was looking for. Ivbarnes's solution worked as presented without any changes.
I truly thought and believed that the solution I accepted was the best at the time and hopefully, no one took offense to my decision. I'll try harder to avoid these situations going forward. My time is limited here at work so I am rushed.
Thanks to all and I appreciate the Experts at Expert Exchange very much!
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39669911
I still don't see a difference... but hey...that's just me.
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39669951
=IF($B$2="","",IFERROR(IF(HLOOKUP($B$2,$C$2:$F$5,
ROWS($A$9:$A9)+1 <-- Changed to ROW($B$2)+(A16-1),
,FALSE)=0,"",HLOOKUP($B$2,$C$2:$F$5,ROWS($A$9:$A9)+1,FALSE)),""))

Below the calendar date table he had 3 rows for the important dates.  When the above formula was dragged down the ROWS($A$9:$A9)+1 returned the same date from the first row.  I added a row number column to his resultset and changed the +1 to a (Result number -1 ) so that it was dynamic and he could add/remove rows.  (Although the important dates will most likely by turned into one date string at some point.)
0
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 166 total points
ID: 39669986
Where is the difference in results in our solutions?... see attached.
Copy-of-HLookup-problem-1.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Frank Freese
ID: 39670014
Thanks for everyones continued interest.
I hope I have not affended anyone - I've always tried to be fair and honest.
0
 
LVL 5

Accepted Solution

by:
Lawrence Barnes earned 167 total points
ID: 39670042
The difference is in the structure.
NB_VC: In the results you were counting the rows of the result set.  Wish I had thought of that as it eliminated my additional row column.  I think because the rows of the result set were used to determine the rows of the source data it is harder for the user to know where to place/troubleshoot the formula.
ssaqibh: The results were based on the row count difference between the result row and the source data, which is not intuitive, although the formula did not change.  (But the user would have had to know to adjust the row number difference constant.
lvbarnes: I set the row number in the results and then found that row number based on the start of the source data so that the results and source are not directly linked.  From a movement standpoint its cleaner and is less work for the user and will convert better to table design.  However, the formula could be cleaned up more to handle additional exceptions.
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39670073
fh_freese:  No worries.  Debating solutions is one of the ways that EE members learn and the EE users get the benefit.  I didn't look at NB_VC's solution but, because he brought it up I have and learned something from it.  I've never used a row range before and he did.  His use of that row range eliminates my need for the additional results column.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39670108
Yes, fh_freeze, lvbarnes is correct.  It's just a point of discussion and I was just looking to clarify and understand why you say some solutions work and others don't.  Please don't think that you have offended anyone.
0
 

Author Comment

by:Frank Freese
ID: 39670138
Thanks to all of you!
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39670227
Yikes!!!
NB_VC
791,247 Expert Points
57,560 Points this month
0
 

Author Comment

by:Frank Freese
ID: 39671620
Folks,
This morning I determined that there were indeed three solutions. In fairness to all is there a way to change the point distribution?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39671626
Click on the yellow tag called "Request attention" at the bottom right corner of your question
0
 

Author Closing Comment

by:Frank Freese
ID: 39673117
thanks to all
I've corrected my error in distributing points
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now