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

Kobi Merrikin

New Member
Joined
Oct 18, 2019
Messages
10
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
 

Attachments

  • HLookup Issue.png
    HLookup Issue.png
    67.4 KB · Views: 73

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Enter =istext(F2) what does it return?
Also enter =istext(the value you know matches) what does it return?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top