Lookup value returns duplicates

Hi,

Here is the sample data.

Column A: Toyota, Kia, Honda, Mercedes
Column B: 4, 5, 6, 4

Column C: Toyota, Toyota, Kia, Honda
Column D: 4, 4, 5, 6

Formula used in Column C: =IFERROR(INDEX(D2,MATCH(D2,$B$2:$B$5,0),1),"")
Formula used in Column D: =IFERROR(SMALL($B$2:$B$5,ROWS($B$2:B2)),"")

As you can see the issue is in Column C where C3 is returning "Toyota" where I want it to return "Mercedes". Due to the complexity of my data and it arrangement, I cannot have a helper column to solve this issue. Could you guys help me with one formula to use in Column C that can fix the issue.

Thanks

PS: This is my first time posting in the forum. Hence, I am not sure if my sample file has been successfully uploaded. Apologies if the file is not available.

You haven't said what you are trying to do and and your formulae are inconsistent with your description. e.g. C does not return Toyota but the number 4

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Looking at your file, what are you trying to accomplish with this ROWS($K$2:K2) in your SMALL function? i ask because as you move down the column N, that range changes.. so not sure what you are trying to do with that.
If all you are wanting is the "Car" name based on the "number" for the month selected, maybe consider sticking with just index/match(preferred) or vlookup

Hi Cubangt,

Thanks for your comments. Column N is to sort the values from Column K in ascending order. ROWS($K$2:K2) helps the SMALL function as it increments going down the column returning the first smallest value, then the second smallest and so forth. The issue is in Column M where the car names are repeating but it should be unique (if you look at the car names in column A. This is what I am trying to achieve.

Since you are doing a "exact match" in the match function, its going to take the first one and return that one always..
Just curious why you cant turn it around and use the car name as your lookup value and return the number, that way you always get your unique car value?

I changed to using your J and K column since that range is returning the correct data, got the zero's to be hidden, but still working on hiding the labels for any that are zero

in the mean time, you can take a look at this post and see if this helps.. If i can get the labels squared away, ill upload the file for you to test.

https://www.excelforum.com/excel-cha...-or-blank.html

Screenshot 2021-06-10 150505.jpg

I think that link would help, seems in order to hide the values that are zero, you need to have your chart use the value for the axis value and then for displaying purpose your configure it to display the name, so that the formula can hide the zero values (which in turn hides the name value as well) at least thats the way i read that post #2 on the link

ill keep trying as i have time, post back if the link doesnt help.. there is a solution, just need to find the right order of things

Here is the file.