LinkBack URL
About LinkBacks
I appears my index match is rendering duplicates.
How I can get the index match to skip any duplicates?
Please Login or Register to view this content.
I appears my index match is rendering duplicates.
How I can get the index match to skip any duplicates?
Please Login or Register to view this content.
Will you please attach a sample Excel workbook? Please don't attach a picture of one.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential information is removed first!!
4. Try to avoid using merged cells. They cause lots of problems!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
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
Hello Glen..
I have uploaded example sheet.
As you can see from the screen shot below, the cells in yellow are incorrect as should be following the sequence of column A on "cheapest" sheet.
Any help would be much appreciated...FYG, I have tried researching online and people talk about using a countif or small/large function but cannot tie this into the formula.
thanks
[image]http://s16.postimg.org/jxg48civ9/indexmatchincorrect.png[/image]
Last edited by projectile; 09-26-2016 at 02:56 PM.
To be honest, I'm not sure what you're trying to achieve with this formula. can you explain? certainly, you can get the result that you want, by making each price unique by adding a trivially small amount to each one (Datasheet column AJ). Then, a simple INDEX-MATCH returns the "correct" result. However, I'm not sure what you are trying to achieve, that =A2 in "Cheapest E2" does not also achieve. Maybe I'm missing the point by a mile...
The "DATASET" sheet contains a list of Lane IDs that different suppliers have quoted on.
column AH of the DATASET sheet contains the name of the supplier and AI contains their Quote for the each LANE ID.
I want to return the cheap price for each lane-id, and the name of the corresponding supplier.
I can return the correct price (column B of CHEAPEST sheet) , however the supplier is incorrect.
I have modified the original formula to render the results of column A of Dataset sheet, for the purpose of this thread, so the example is clear cut. The original formula returns would normally column AH ...
i.e.
=IF(T2=0,"",INDEX(DATASET!$AH$2:$AH$1455,MATCH(MIN((IF(DATASET!$A$2:$A$1455=A2,IF(DATASET!$AI$2:$AI$1455<>0,DATASET!$AI$2:$AI$1455)))),DATASET!$AI$2:$AI$1455,0)))
hope this makes sense.
I'm away from my PC, Excel, etc. Csn you explain how the formula I provided does not work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks