Index match formula is not pulling the correct data
I'm attempting to pull data from a large spreadsheet based on a SKU, a quantity with a per quantity price plus a flat fee. The match formula is not matching to the correct row no matter what I do. Any help would be greatly appreciated. The attached spreadsheet contains a small subset of the data I'm attempting to retrieve via the formula.
Thanks,
Tom
If you could add the expected results in the workbook as well it would be much easier to help you.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
What are you trying to get the Index Match to do? Currently it and the VLOOKUP are both returning 5556.
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Here's an updated spreadsheet with the expected results. Thanks for your help.
Tom
It supposed to add the base price of 5556 to the multiplication of the quantity of 50,000 times the FMV price for that quantity, ie. 0.058474752. The total is 8479.74
thanks
So why is B12 in the MATCH formula then? You are searching for 50 000 in column H which only contains 15 000 and 15 001.
It's supposed to pull the price based on the lookup value of 50000 and the range of 15,001 and 100,000,000,000. By entering a 1 in the match formula, it should find the largest value that is less than or equal to the lookup value.
I think the reason it's not working is that the 1 option in MATCH only works with a sorted column.
I made a new formula that I think works but it looks terrible. I hope some other member comes along and improves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks