• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HLOOKUP Help

P

PaulFogel123

Member
I use hlookup formulas in my forecasting program to find dates from a data worksheet located on another tab. The formula references the proper month (the lookup value) and finds the right data from the data worksheet. Here is one such formula, simplified a bit:
=IF((HLOOKUP(CA4,Historical_Data,96)=FALSE),500,HLOOKUP(CA4,Historical_Data,32))

It looks for a test on row 96 in the database, and if true, returns a value from row 32 in the database. So far, so good. But if I add rows to my database to add new fields, the formula still looks for rows 96 and 32.

One solution is to use ROW('Historical_Data'!A96) and ROW('Historical_Data'!A32) for row_index_num in the above hlookup formulas. That way, a row inserted in the database will cause the row references to change too. But, is there a better or more elegant way to do this? Is MATCH/INDEX a better way to go?
 
Maybe you could provide your sample workbook with the expected results, need to see the situation...

Thanks
 
Hi Paul ,

I am not able to understand the syntax of your HLOOKUP usage ; if you omit the fourth parameter of the HLOOKUP function ( which is either TRUE / FALSE or 1 / 0 ) , it defaults to a value of TRUE. In this usage , the HLOOKUP function returns an approximate match , which may or may not be the correct thing to do. I assume you are aware of this.

If the HLOOKUP function does not find a match ( where the looked up value is less than the minimum value in the lookup range ) , then it will return a #N/A error value , which needs to be trapped using an ISNA function or ISNUMBER function ; I am not sure the way you are comparing the result to a FALSE value is correct.

I think it should be :

=IF(ISNA(HLOOKUP(CA4,Historical_Data,96,FALSE)),500,HLOOKUP(CA4,Historical_Data,32))

where the second HLOOKUP can be an approximate match.

Be that as it may , the INDEX / MATCH combination is not going to be more elegant , since that will also have to cater to row insertion / deletion.

Another suggestion is to match the row headers if there are any , to arrive at the correct row to use ; this will do away with the 96 and the 32 , and will be immune to row insertion / deletion.

Narayan
 
azumi said:
Maybe you could provide your sample workbook with the expected results, need to see the situation...

Thanks
Click to expand...

azumi said:
Maybe you could provide your sample workbook with the expected results, need to see the situation...

Thanks
Click to expand...
Since the workbook is a monster with many interrelated parts, I reduced it to just Employee Benefits so you can see how the formula works.
Thanks so much for taking a look at this.
 

Attachments

NARAYANK991 said:
Hi Paul ,

I am not able to understand the syntax of your HLOOKUP usage ; if you omit the fourth parameter of the HLOOKUP function ( which is either TRUE / FALSE or 1 / 0 ) , it defaults to a value of TRUE. In this usage , the HLOOKUP function returns an approximate match , which may or may not be the correct thing to do. I assume you are aware of this.

If the HLOOKUP function does not find a match ( where the looked up value is less than the minimum value in the lookup range ) , then it will return a #N/A error value , which needs to be trapped using an ISNA function or ISNUMBER function ; I am not sure the way you are comparing the result to a FALSE value is correct.

I think it should be :

=IF(ISNA(HLOOKUP(CA4,Historical_Data,96,FALSE)),500,HLOOKUP(CA4,Historical_Data,32))

where the second HLOOKUP can be an approximate match.

Be that as it may , the INDEX / MATCH combination is not going to be more elegant , since that will also have to cater to row insertion / deletion.

Another suggestion is to match the row headers if there are any , to arrive at the correct row to use ; this will do away with the 96 and the 32 , and will be immune to row insertion / deletion.

Narayan
Click to expand...
 
I used the formula syntax this way to match the dates better between the two sheets. On the Historical Actuals DB tab, someone other than me could input a date for the month, choosing the 2nd day of the month, or even the last day. On the Income Statement tab, I have the dates locked down to be the last day of the month. This way, when the lookup formula in the Income Statement tab goes searching in the Historical Actuals DB tab, it will pick up any date in that same month. If I want the value for June 2014, I'll pick it up Historical Actuals DB tab, whether it's June 10 or June 20 or any date in June. Makes sense?

Please take a look at the file I just posted.

You wrote, "Another suggestion is to match the row headers if there are any , to arrive at the correct row to use ; this will do away with the 96 and the 32 , and will be immune to row insertion / deletion." That seems like an excellemt idea, but how does one match the row headers?

Thanks
 
Hi Paul ,

See your file now. I have put in the formula in the first cell. I have created 3 named ranges :

Dates , Data_Load_Complete , Employee_Benefits

Narayan
 

Attachments

NARAYANK991 said:
Hi Paul ,

See your file now. I have put in the formula in the first cell. I have created 3 named ranges :

Dates , Data_Load_Complete , Employee_Benefits

Narayan
Click to expand...
Amazing. It's completely different from what I had. This would seem to do the job (I think). Can you walk me through the formula?
 
Hi Paul ,

I should tell you that I have put in a formula in the Dates row also , so that all of the dates refer to the end of the month. This ensures that if you put in a date on the Income Stmt sheet , it can be found in the Dates row on the Historical Actuals DB tab.

Basically we are creating an array of values where the row of dates equals the date in the Income Stmt tab ; clearly there will be just one cell in the range where there is a match. Corresponding to this cell , we look at the cell in the Data_Load_Complete row ; if it has a TRUE value , we return 500,000 else we look at the corresponding cell in the Employee_Benefits row.

So now , you can add and delete rows , but the named range will ensure that the correct rows are always used.

The only thing that you should not do is use some other rows for these two purposes ; in case you decide to use say row 53 and row 22 , you will need to change the reference using the Name Manager.

Narayan
 
NARAYANK991 said:
Hi Paul ,

I should tell you that I have put in a formula in the Dates row also , so that all of the dates refer to the end of the month. This ensures that if you put in a date on the Income Stmt sheet , it can be found in the Dates row on the Historical Actuals DB tab.

Basically we are creating an array of values where the row of dates equals the date in the Income Stmt tab ; clearly there will be just one cell in the range where there is a match. Corresponding to this cell , we look at the cell in the Data_Load_Complete row ; if it has a TRUE value , we return 500,000 else we look at the corresponding cell in the Employee_Benefits row.

So now , you can add and delete rows , but the named range will ensure that the correct rows are always used.

The only thing that you should not do is use some other rows for these two purposes ; in case you decide to use say row 53 and row 22 , you will need to change the reference using the Name Manager.

Narayan
Click to expand...
Narayan,

Three followup questions for the formula you wrote:
IF(OR((Dates=D$4)*Data_Load_Complete),500000,SUMPRODUCT((Dates=D$4)*Employee_Benefits))
  • If the OR function is testing for a match for the date and Data_Load_Complete=TRUE, shouldn't it be AND?
  • What does the asterisk (*) mean, if not multiplication? Is that the nomenclature for SUMPRODUCT?
  • Since the first argument in formula is TRUE, why is 500000 not returned?
 
Hi Paul ,

In the formula :

IF(OR((Dates=D$4)*Data_Load_Complete),500000,SUMPRODUCT((Dates=D$4)*Employee_Benefits))

the arrays are being created by entering the formula as an array formula , using CTRL SHIFT ENTER.

The segment (Dates=D$4) creates an array of TRUE / FALSE values , TRUE where the date in the named range Dates is equal to the date in cell D4. When we multiply this array by the TRUE / FALSE values in the named range Data_Load_Complete , we get an output array of 1s and 0s ; 1 where both the first array and the named range Data_Load_Complete have a TRUE value , and FALSE otherwise. The multiplication is in effect performing an AND. The OR is required to get an OR of all these values ; what we would like to know is whether there is at least one TRUE value in both arrays in the same location.

Narayan
 
NARAYANK991 said:
Hi Paul ,

In the formula :

IF(OR((Dates=D$4)*Data_Load_Complete),500000,SUMPRODUCT((Dates=D$4)*Employee_Benefits))

the arrays are being created by entering the formula as an array formula , using CTRL SHIFT ENTER.

The segment (Dates=D$4) creates an array of TRUE / FALSE values , TRUE where the date in the named range Dates is equal to the date in cell D4. When we multiply this array by the TRUE / FALSE values in the named range Data_Load_Complete , we get an output array of 1s and 0s ; 1 where both the first array and the named range Data_Load_Complete have a TRUE value , and FALSE otherwise. The multiplication is in effect performing an AND. The OR is required to get an OR of all these values ; what we would like to know is whether there is at least one TRUE value in both arrays in the same location.

Narayan
Click to expand...
Thank you so much for your time and expertise.
Paul
 
And yet...
When I look at the formula closely, I'm having trouble making it work.
If the first expression in =IF(OR((Dates=D$4)*Data_Load_Complete),500000,SUMPRODUCT((Dates=D$4)*Employee_Benefits))
is TRUE, why is the formula not returning 500000?
 
Hi Paul ,

Sorry , but I think I have not array-entered the formula , though I have mentioned it in my explanation in a later post.

If you do array-enter the formula , you will get 500 ; the 500,000 is being displayed as 500 due to the cell format.

Narayan
 
I'm a novice at array formulas, but it works! If I enter the formula with Control-Shift-Enter I get the array formula; if I manually insert { and }, it doesn't work. Not sure why that is. However, once created successfully, the formula can be copied correctly.
 
You must log in or register to reply here.
Back
Top