Vlookup

[crumpets266]

Hi, i recently followed this tutorial for vlookup
http://www.howtogeek.com/howto/13780...okup-in-excel/
and everything worked out well, except for one problem. What ive formulated is that when i type a job number, the correct job name is displayed in the cell next to the job number. But when there is a blank cell, the number 02011 shows up?? see the pic i have attachedexample.jpg

[crumpets266]

What i want is obviously a blank cell. Any suggestions?

[y_not]

=IF(VLOOKUP(A18,'[JOB LIST 7.xls]Master Job List'!$A$10:$B$4126,2,FALSE),<0,"",VLOOKUP(A18,'[JOB LIST 7.xls]Master Job List'!$A$10:$B$4126,2,FALSE

I'm not that bright but I think this will work

[TMS]

Pictures of worksheets are not very helpful in diagnosing problems or providing robust solutions. I can't even copy the formula to edit it.

Regards, TMS

[crumpets266]

Unfortunately excel isn't happy with this formula.

[crumpets266]

should i upload an excel file? sorry, im new to this forum

[y_not]

TMShucks is more likely to be able to help if you upload a sample file - just be sure to remove any sensitive/personal data first

[TMS]

should i upload an excel file? sorry, im new to this forum

Yes please. I don't think the formula offered is far off ... Clearly it had to be typed from scratch and it looks like a comma that' snot required.

You can try:

Please Login or Register  to view this content.

Thanks to y_not for typing it out.

Regards, TMS

[crumpets266]

Hi TMS,
Surely if I upload an excel file it will show errors due to my vlookup formulas in column b? I tried your formula and it still yields a result of 02011. I have no idea where this 02011 is coming from!

[Jonmo1]

Surely if I upload an excel file it will show errors due to my vlookup formulas in column b?

Yep, that's the idea.
We will be able to see the formula that isn't working correctly.
And we will hopefully be able to see what is causing that formula to not work correctly.
It's VERY difficult to see what is the cause by just a picture.
And actual Excel file helps alot more.


Just taking a guess,
It's likely that the values in column A are a formula
And when it gets down to A16, that formula is returning a "" value.
PLUS
The values in MasterJobList column A are also a formula, and some are returning ""
So the vlookup is finding an exact match for "" in the lookup table, and returning whatever is in column B

[crumpets266]

Precisely TMS,

The values from the MAster Job List Column A all have formulas. Is there any way around this? I'd rather avoid uploading the excel file if that is okay, just because it doesnt belong to me, im just editing it.

[crumpets266]

The column A, on the sheet where the 02011 shows up, does not have any formulas though. it is just blank.

[Jonmo1]

column A, on the sheet where the 02011 shows up, does not have any formulas though.

Perhaps it was a formula at one time that has since been replaced by values by copy / Paste Special / Values
If so, then it still contains an empty text string value (which is what "" actually is)

Right click on A16 and choose 'ClearContents'

[TMS]

@crumpets266: if you upload a sample file we will be able to tell you definitively where it is getting the number from, and why, and suggest some options to fix it or work around it.

If you want to keep playing guessing games, it will take a lot longer ... at least until we get bored.

[crumpets266]

2013 timesheets edited.xlsxJOB LIST 7.xlsx

Sorry for time wasting. I have attached the two sample files.

[TMS]

OK, on row 3232 of the Current Jobs sheet, in column A, Job Name, you have the value 2011 which would appear to be a "year break" in your job name list. In column B, Job No., you have the value 0. These values are copied across to the Master Job List but the columns are swapped and the Job No. appears in column A and the Job Name is in column B, as before on row 3232. Many of the rows on both sheets are hidden so it's not immediately obvious.

Now, the "blank" entries in column A on the time sheet are being "interpreted" as zero (0) and hence they return the year, 2011. This is displayed as 02011 because of the "special" zip code format on the column.

Not sure why those blank entries are zero (probably looked at it too long) but, to stop it going there ...

in cell B16:

Please Login or Register  to view this content.

Regards, TMS

[crumpets266]

Amazing, thank you so much. Works perfectly.

[TMS]

You're welcome.