Strange VLOOKUP problem.

[Steve_in_Kent]

Two tabs in a worksheet. one tab looks up data in the other tab.

Tab 1.. Column A contains a number stored as general. ie: 123456

Tab 2.. Column D contains a similar number, stored as general 123456

Formula in column O on Tab2 .. =IF(VLOOKUP(D2*1,'Tab1'!A:I,8,FALSE)="","Forget",IFERROR(VLOOKUP(D2*1,'Tab1'!A:I,8,FALSE),"Missed"))

It pulls a date from column I

For the first few rows, in column O of tab 2,, its returning #N/A , then correctly returns the date in all further rows.

Both A and D columns, are set to general. the formula = IF(O2=Tab1D2,1,0) .. returns true. O2 does equal D2.. but the formula returns an error. (there is a date in column I on tab 1)

I've narrowed it down to the initial lookup part D2*1. If i change the first 6 rows formula to just D2, the first 6 rows will then correctly return the date. but the rest of the rows do not.

[HansV]

This suggests that some of the values are seen as text, others as numbers. Try the following:

Set the horizontal alignment of column D on Tab to General. Are some values left-aligned and others right-aligned?

Do the same for column A on Tab1.

By the way, your formula retrieves values from column H, not I, since H is column 8.

[Steve_in_Kent]

Hans,
Both columns A and D are set to General.

Tab2 D is left aligned. Tab1 A is right aligned.. but changing them makes no difference

and yes.. sorry, column H (there is a date in there)

[HansV]

But did you try my suggestion of setting horizontal alignment to General? You do this in the Alignment tab of the Format Cells dialog.

[Steve_in_Kent]

ahh yes Hans.. that makes no difference.

[Steve_in_Kent]

cut down attachment, demonstrating error attached.

[Rudi]

You may have misunderstood Han's advice about setting horizontal alignment to General. It is not setting the cell format to general, but rather to remove the left alignment of the first sheet. Select column A and deactivate the align left button on the Home ribbon. You will see that the data will realign to the natuarl alignment dictated by Excel (that is: number align right and text aligns left). So the alignment does not fix the formula, but it shows you that your column A has a mixture of text and numbers...and this is the cause of your error.

To fix the error, you need to convert the text numbers in column A to actual numbers. Follow these steps:
Select the range in column A
Go to the DATA ribbon and click on Text To Columns
Immediately click Finish
The text numbers should convert to actual numbers and the formula in O and P will rectify.

[Steve_in_Kent]

Thanks Rudi.. that explains the issue. and i can see it sort of fixes the error.. however, whats strange

is that it doesn't explain how Excel using =IF x = y confirms they are they same, yet Vlookup fails.

right clicking each comparison cell, shows they are the same, General. !!

(it imports from another programme).. would a fix be to say, point say a Z column and say Z=A then use the Z column
for the Vlookup ?

[Rudi]

If you don't want to fuss with correcting value in the table array (source table in first sheet) based on if it is a numeric or text, you can just script it into the formula. It looks terrible, but the following formulae calculate if it is numeric or text.

Please check and verify that the two formulas are indeed calculating correctly and return the expected results....

For cell O2 in the second sheet use this formula and autofill down:

Code: Select all

=IF(IFERROR(VLOOKUP(D2*1,'Count Sheet'!A:I,8,FALSE),VLOOKUP(TEXT(D2,"@"),'Count Sheet'!A:I,8,FALSE))="","NOT COUNTED",IFERROR(IFERROR(VLOOKUP(D2*1,'Count Sheet'!A:I,8,FALSE),VLOOKUP(TEXT(D2,"@"),'Count Sheet'!A:I,8,FALSE)),"MISSING FROM COUNT SHEET"))

For cell P2 in the second sheet use this formula and autofill down:

Code: Select all

=IF(IFERROR(VLOOKUP(D2*1,'Count Sheet'!A:I,9,FALSE),VLOOKUP(TEXT(D2,"@"),'Count Sheet'!A:I,9,FALSE))="","NO INPUT",IFERROR(IFERROR(VLOOKUP(D2*1,'Count Sheet'!A:I,9,FALSE),VLOOKUP(TEXT(D2,"@"),'Count Sheet'!A:I,9,FALSE)),"MISSING FROM COUNT SHEET"))

[Steve_in_Kent]

Many thanks.. long!. will look at it again.

many thanks.

[Rudi]

To step through the formula, use the Evaluate Formula feature on the FORMULAS ribbon.
Its a useful tool to review how Excel calculates to the final value and you can use it to debug long formulas.