Vlookup returning wrong data occasionally

[Factor 21]

Hi,

I have a VLOOKUP on the Emplyee Leave Tracker tab of the attached workbook in column I, this to the table LucnchHours and return the value in column 5 of the table.

The cells highlighted yellow on the tab are returning the wrong result, it would appear from the cell above the correct answer, I have tried different formats for the table colums and referenced different columns but the answers remain incorrect or the highlighted cells.

Does anyone have any ideas why this could be happenning?

Thanks in adavance

Jeremy

 

[rrocker1405]

Hi Jeremy,

This was basically because of the formatting issue. I cleared the format and applied another time format to make it working for you.

Hope this helps

Kind regards,
A!

 

[David Evans]

Morning Jeremy -

It appeared to me that the cell was evaluating to a fractional amount and the lookup was consequently finding the nearest value ... I amended the formula to roundup your lookup and it seems to correct the issue
=VLOOKUP(ROUNDUP(H6,1),LunchHours,5)

Dai

 

[John Jairo V]

Hi to all!

The problem isn't format, Vlookup neither. The problem is for binary calculation for excel, and floating decimals. There are many ways to solve the problem... I give you 2:

1. Instead of Vlookup, use this formula:
=INT(3*H6)/24
This formula not depends on the base table in Settings sheet.

2. If you want to use Vlookup, fix the hours from the base table (In Settings Sheet)

a. Put in K4 1:00
b. Select K4, go to Home Tab, Group Editing, Option Fill - Series
c. Series in Column, Step Value: 1:00, Stop Value: 176:00.​

And check the results in your sheet.

I hope it helps. Blessings!

 

[Factor 21]

Hi Jeremy,

This was basically because of the formatting issue. I cleared the format and applied another time format to make it working for you.

Hope this helps

Kind regards,
A!

Hi A!,

This has not resolved all the issues on the sheet.

Thanks for looking though

Jeremy

 

[Factor 21]

Morning Jeremy -

It appeared to me that the cell was evaluating to a fractional amount and the lookup was consequently finding the nearest value ... I amended the formula to roundup your lookup and it seems to correct the issue
=VLOOKUP(ROUNDUP(H6,1),LunchHours,5)

Dai

Hi Dai,

This worked fine for me thanks.

regards

Jeremy

 

[Factor 21]

Hi to all!

The problem isn't format, Vlookup neither. The problem is for binary calculation for excel, and floating decimals. There are many ways to solve the problem... I give you 2:

1. Instead of Vlookup, use this formula:
=INT(3*H6)/24
This formula not depends on the base table in Settings sheet.

2. If you want to use Vlookup, fix the hours from the base table (In Settings Sheet)

a. Put in K4 1:00
b. Select K4, go to Home Tab, Group Editing, Option Fill - Series
c. Series in Column, Step Value: 1:00, Stop Value: 176:00.​

And check the results in your sheet.

I hope it helps. Blessings!

Hi John,

Thanks for the response, I have implemented the 1st option in my sheet as after much reading I get the impression that VLOOKUP can be tempremental within excel.

Many thanks

Jeremy