Solved How to HLOOKUP a date from another tab with condition

February 23, 2017 at 08:03:20
Specs: Windows 7
Basically I want to pull the correct date that is associated with a job number (the matching condition) from one tab into another.

A sample file is posted here.
Book3.xlsx
http://www57.zippyshare.com/v/VGlLK...


See More: How to HLOOKUP a date from another tab with condition

Report •

#1
February 23, 2017 at 11:44:33
All your Job Numbers on Sheet 2 are formatted as TEXT,
while all the Job Numbers on Sheet 1 are formatted as NUMBERS.

Change the formats on Sheet 2 to NUMBERS

MIKE

http://www.skeptic.com/


Report •

#2
February 23, 2017 at 13:17:04
I checked that. When I open the file the dates are formatted as numbers. That ain't it.

Report •

#3
February 23, 2017 at 13:59:29
✔ Best Answer
When I open the file the dates are formatted as numbers.

That's because Excel stores Dates as the number of days since Jan 1900.
Make sure they are really Numbers, not TEXT that look like numbers.
Change the format to a Date Format.

That ain't it.

When I accessed the file from Zippyshare,
the first thing I noticed on SHEET 2
was the small green error marker on Row 3 which reads:

The number in this cell is formatted as text or preceded by an apostrophe

I changed the Job Number to a real number and the lookup worked.

If you do not see the green error marker, then most likely
it has been turned off in the options section.
To turn it back on:

1)Click the File tab.
2)Click Options.
3)Click the Formulas category.
4)Down the bottom under Error checking rules,
Check the Numbers formatted as text or preceded by an apostrophe check box.
Click OK.

You should additionally see the error marker on Sheet 2 Row 7 the Profit Center, and on Sheet 1 Column C, FY.

Finally, on Sheet 1 Column D, Events, don't show up because you referenced the wrong row, it should be 2 not 1, something like:

=HLOOKUP(G3,Sheet2!$B$3:$BA$4,2,0)

From the type of problems your having,
I would guess that you copied this data from someplace else
and pasted it into excel.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
February 23, 2017 at 14:16:16
That was it. It's interesting that even though in the dropdown it shows correct, it's not really. It does work now. Is there any way to correct the same error all at once. Having to do each one does take a while.
Thank you so much for your help. Greatly appreciated!

Report •

#5
February 23, 2017 at 14:38:36
To correct the Job Numbers on Sheet 2:

In cell B2 enter the formula: =IF(B3="","",VALUE(B3))

Drag the formula, right, along row 2 to column BA
On the Home Tab select Copy
On the Home Tab select Paste Values

Row 2 should now have the Job Numbers as real numbers.

Just copy/paste B2:BA2 to B3:BA3

MIKE

http://www.skeptic.com/


Report •

#6
February 23, 2017 at 14:41:30
Got it. Thanks a bunch!

Report •

Ask Question