Dear sir,
I want to find the value between the numbers using the vlookup formula. But the formula is doing something wrong. You see Sheet2 in column "B" the serial number 70 they it did not come. Do not know what is wrong, if you can, give a new formula.
Thank you.
Sorry for my bad english
The data has to be ascending order: your is not.
Value for 70 is less than value for 69 so will match on 100615.7076 (69) as this the nearest (lower) value to the search value.
The data in column C MUST to be SORTED for inexact match to work correctly
That could not be done because of your merged cells
So I removed columns A & B to get the formula to work
Rebuild your sheet without merged cells, sort your data, and all should be good
Last edited by kev_; 01-26-2018 at 10:15 AM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
hello sir,
Yes is correct but I can not sort the list, it should be in the same place. Is there any other formula or way?
hello sir,
Yes is correct but I can not sort the list, it should be in the same place. Is there any other formula or way?
Here is a way to get your result without sorting your data.
B2 of Sheet2 =SUMIFS(Sheet1!E:E,Sheet1!C:C,"<="&A2,Sheet1!D:D,">="&A2)
Try this formula:
=INDEX(Sheet1!$E$2:$E$95,MATCH(1,INDEX((Sheet2!A66> =Sheet1!$C$2:$C$95)*(Sheet2!A66< Sheet1!$D$2:$D$95),0),0))
as far as I can see it is giving the correct answers in rows 52-75....
Last edited by Glenn Kennedy; 01-26-2018 at 11:02 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Use 2 linked lists on 2 different sheets
- leave List1 in the original order
AND
- sort List2
- base formula on List2
63falcondude,
and also thank you very much for giving formula.
Glenn Kennedy,
Thank you very much, this is working very well.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
You're welcome. Thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks