HLookup not working when using a cell with a formula in as the lookup value

[Kobi Merrikin]

Hi All,

Thanks in advance for any help
I am trying to use a HLookup to return data from a table of which the lookup value references a different cell which creates a value using a formula.

My company working in "work weeks" so I am using a vlookup to use search for last weeks work week on our work calendar =VLOOKUP(TODAY()-7,Lookup!A1:B367,2,0). This then returns the value "202017" sitting in cell F2. I am then trying to use this as a reference for the HLookup =HLOOKUP(F2,Table1[#All],2,0) however this is returning a #N/A. When I change the lookup value in the Hlookup to "202017" it works fine. I have ensured there are no spaces in either occurrences of the number and have even copy and pasted back as values but this doesn't seem to help.

Any advice/ideas would be great! I have included a snippet showing the formulas

Thanks
Kobi

 

[gaz_chops]

Enter =istext(F2) what does it return?
Also enter =istext(the value you know matches) what does it return?

 

[Kobi Merrikin]

Enter =istext(F2) what does it return?
Also enter =istext(the value you know matches) what does it return?

Both return "FALSE". Does that imply its formatted wrong?

 

[Kobi Merrikin]

Ah I've got it to work now thanks so much for this! I have formatted the first Vlookup as Text!
You're a star, thanks!

 

[gaz_chops]

You're welcome, the problem is often due to one value being text.