Hi All,
I am having a major problem when using lookup formula. The lookup table is as below but the value for 8 > 20 always returns 3 instead of the 0.25. Is it the way I have the lookup table please? The formula I am using is =LOOKUP(K9,Sheet4!I6:J11) and on Sheet 4 is the look up table:
8 > 20 0.25
16 > 26 0.5
22 > 36 0.75
30 > 50 1
40 > 60 2
60+ 3
Many thanks.
without seeing your data, it's hard to tell (for me at least).
post the excel file, without confidentional information.
please also add the desired result.
I think a VLookup will solve your problem.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Your table doesn't make any sense (to me!).
What does this mean:
8 > 20
Does it mean from 8 to 20 (inclusive)?
If so, then you have overlapping levels. For example, if the lookup value is 17 which level does it fall into? It could fall into 8 > 20 or 16 > 26.
Need some clarification.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Both,
I think I have sorted it. The problem appears to have been the 8 > 20 cell. I changed this to <20 and the lookup now works.
Thanks for your time.
If 8 > 20 is text in 1 cell, then maybe this?
=VLOOKUP(K9,Sheet4!$I$6:$J$11,2,0)
If you are trying to say that 8 > 20 is a range from 8 to 20, then, as Tony mentioned, you have some serious overlaps there that you need to resolve 1st
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Good deal. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks