SOLVED

HLOOKUP looking for a value that is the result of a formula

Copper Contributor

Hello,

 

This is my 'Coef_Table':

half day shifts1234567
10.2560.4720.9851.3470.6391.6650.398
21.8540.5240.6581.4740.5741.2141.658

 

In another table:

datehalfcoeficient
01/01/20111=HLOOKUP(WEEKDAY(A2);'Coef_Table!A1:H3';(B2+1);FALSE)
01/02/20112=HLOOKUP(WEEKDAY(A3);'Coef_Table!A1:H3';(B3+1);FALSE)
01/03/20111=HLOOKUP(WEEKDAY(A4);'Coef_Table!A1:H3';(B4+1);FALSE)

 

HLOOKUP does not lookup for the value returned by WEEKDAY() and aldo does not respect the line number argument as a result of 'half+1'

 

Is there a way to pass arguments to HLOOKUP as results of formulas?

 

4 Replies

@mlohmann Yes you can. Just make sure that the numbers in the top of the Coef_Table are real numbers and not texts.

@Riny_van_Eekelenthanks for your answer, but this is not working even tho the numbers in the first line of the table are real numbers. In fact, if I replace the formula with a typed value it works! I can even format the values with trailing decimal zeros, just to confirm they are real numbers, but as soon as I get the value from a formula the lookup fails.
I also tryed to externalize the formula, putting into C2 [=WEEKDAY(A2)] then LookUp for C2 instead of the formula, and it still returns #N/A

best response confirmed by Grahmfs13 (Microsoft)
Solution

@mlohmann I copied your example date into an empty sheet and entered your formula as well and all seems to work OK, provided that you remove the apostrophes surrounding the range to 'Coef_Table!A1:H3', and make the reference to A1:H3 absolute. If you insist to use apostrophes the need to surround the sheet name only. Do include the cell ranges.

 

See attached for a working example.

Thanks again for your answer and example! I see it working but I can´t see the difference in my file. The apostrophes surrounding the range were just in the smal sample I typed here, It was correctly surrounding the sheet name only within the actual formula, since it was placed automaticaly by excel (sorry I mistyped here). I´ll review the whole thing then, since your example works fine, there is surrely something in my data!

Best regards!
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@mlohmann I copied your example date into an empty sheet and entered your formula as well and all seems to work OK, provided that you remove the apostrophes surrounding the range to 'Coef_Table!A1:H3', and make the reference to A1:H3 absolute. If you insist to use apostrophes the need to surround the sheet name only. Do include the cell ranges.

 

See attached for a working example.

View solution in original post