Vlookup not working and it's driving me MAD!!!

kristylee

New Member
Joined
Aug 28, 2006
Messages
21
Would anyone be willing to look at it for me and try to solve why it's not working right?? Would be happy to email it for help!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Thank you but I don't think it's any of these issues. I am truly stumped and have spent all day trying to solve the problem. :(
 
Upvote 0
Can you save the workbook on Google Drive, and post the link?
 
Upvote 0
I can understand having that general rule but I am so desperate to get this figured out and can't think of any way besides having someone review the actual file. :(
 
Upvote 0
Hi kristylee,

In what way lookup is not working? Why don’t you post a sample data here ?
 
Upvote 0
I can understand having that general rule but I am so desperate to get this figured out and can't think of any way besides having someone review the actual file. :(

You can either use one of the add-ins here that enable you to include some sample in your post https://www.mrexcel.com/forum/about-board/508133-attachments.html
Or you can look at the suggestion made in post#4

All correspondence should be kept on the board, no matter how desperate you are.
 
Upvote 0
I can understand having that general rule but I am so desperate to get this figured out and can't think of any way besides having someone review the actual file. :(
Not trying to pick on you, but we have a set of rules & guidelines and everybody has to be bound by them. If urgency is an important factor, you should take note of 5g and 5h of the Forum Use Guidelines
 
Upvote 0
Here's what I'm seeing that is giving the result of 40, instead of 184



I found your problem.

The formula needs to be changed from:

Code:
=IF(FH7=5.5,VLOOKUP($H7,'Kronos Accrual Profiles & Maxes'!$A$1:$E$48,5),
IF(FH7=3.5,VLOOKUP($H7,'Kronos Accrual Profiles & Maxes'!$A$1:$D$48,4),
IF(FH7=2.5,VLOOKUP($H7,'Kronos Accrual Profiles & Maxes'!$A$1:$C$48,3),
VLOOKUP($H7,'Kronos Accrual Profiles & Maxes'!$A$3:$B$48,2))))
To:

Code:
=IF(FH7=5.5,VLOOKUP($H7,'Kronos Accrual Profiles & Maxes'!$A$1:$E$48,5,[COLOR=#ff0000]FALSE[/COLOR]),
IF(FH7=3.5,VLOOKUP($H7,'Kronos Accrual Profiles & Maxes'!$A$1:$D$48,4,[COLOR=#ff0000]FALSE[/COLOR]),
IF(FH7=2.5,VLOOKUP($H7,'Kronos Accrual Profiles & Maxes'!$A$1:$C$48,3,[COLOR=#ff0000]FALSE[/COLOR]),
VLOOKUP($H7,'Kronos Accrual Profiles & Maxes'!$A$3:$B$48,2,[COLOR=#ff0000]FALSE[/COLOR]))))

In summary, the root cause for your issue was the initial formula is looking for a TRUE result.
Using FALSE gives an exact match, while leaving out FALSE does the ladder for TRUE.

I provided a screen shot so other members going forward can witness it.

 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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