How do you create a dynamic HLOOKUP formula that returns a specific value?

[LLQ]

I need some help, this is my first time on this forum.

I got as far as returning the value I wanted if there is data in my spreadsheet with my HLOOKUP formula, however I want to make it dynamic so that when I add a row of data I don't have to keep changing the formulas. Here is the formula I am using =IF(HLOOKUP($DB$6,$CM$1:$CZ$73,9,FALSE)>0,1,HLOOKUP($DB$6,$CM$1:$CZ$73,9,FALSE)).

Thanks if anyone can help with this one it would be great.

[JBeaucaire]

Try taking out the row references.


=IF(HLOOKUP($DB$6,$CM:$CZ,9,FALSE)>0, 1, HLOOKUP($DB$6, $CM:$CZ,9,FALSE))



This formula looks weird to me. If you posted a desensitized copy of you workbook and show us, perhaps I can be sure in my suggestion.

[Pete_UK]

HLOOKUP will search the values in the row CM1:CZ1 looking for an exact match, and if it finds one it will return the data from the 9th row in that matching column (because of your 9). So, I'm not sure why your table is defined as going down to row 73, and if you add more data below that then the data returned will still be from the 9th row.

Nevertheless, you could change it to $CM:$CZ to use full column references.

Hope this helps.

Pete

[LLQ]

thanks Pete, the column reference you referred to does not work to replace a row reference, I get an error.

Any other suggestions would be appreciated.

Linda

[Pete_UK]

Ah yes, I tend to use VLOOKUP rather than HLOOKUP, and so you can remove the column references and have full-row references:

$1:$73

However, as I pointed out you will not get data beyond the 9th row unless you do something about the third parameter.

Hope this helps.

Pete

[LLQ]

Thanks Pete - I think I need to rework my spreadsheet - that reference won't work either.

[LLQ]

Hi Jerry, thank you, I tried your suggestion to no avail. I need to rethink my entire spreadsheet, it's 1.5 years old, I've advanced since then, so think I need to rework to take advantage of pivots.