1

I have a list of values in column A on an excel TAB and on the column B next to it I have a list of words. On another TAB, I'm trying to retrieve the words from B where A has the smallest value, then the 2nd smaller value, 3rd, etc.

Example: Column A would have (2,3,3,6,8) and B (car,dog,cat,house,boat)

I firstly created a column where I retrieved the smallest values:

  • On column X1 I added: SMALL('Table'!$A:$A,1)
  • On column X2 I added: SMALL('Table'!$A:$A,2)
  • etc...

and then a VLOOKUP worked for me:

  • On column Y1 I added: VLOOKUP(X1,'Table'!$A:$B,2,FALSE)
  • On column Y2 I added: VLOOKUP(X2,'Table'!$A:$B,2,FALSE)

So far so good. My issue is when my values repeat. *i.e.: In the example above, whenever the function finds the value 3 in column A, I get the word dog twice instead of dog and cat because it just displays the first value the vlookup finds.

I've tried adding an offset: =OFFSET(SMALL('Table'!$A:$A,1),1,0) but not sure if it works to fetch from other TABS.

Any help or workaround for this please? Many thanks in advance.

pedroteixeira07
  • 91
  • 11

1 Answers1

1

I suggest using Index/match instead of VLOOKUP and adding one to the match position if the same value has occurred once before, two if it has occurred twice before etc.:-

=INDEX(Table!B:B,MATCH(A2,Table!A:A,0)+COUNTIF(A$1:A1,A2))

You can also use something like this to get the smallest, second smallest etc. rather than hard-coding it:-

=SMALL(Table!$A:$A,ROW(1:1))

enter image description here

OK the trick I'm going to use to separate rows with equal values is to add a small amount to each row to make the value unique. Here are my formulae:-

=LARGE(IF(A$2:A$9="Restaurants",C$2:C$9),ROW(1:1))

to get the largest value, this is a standard formula

=INDEX(D$2:D$9,MATCH(LARGE(IF(A$2:A$9="Restaurants",C$2:C$9+ROW(C$2:C$9)/10^7),ROW(1:1)),C$2:C$9+ROW(C$2:C$9)/10^7,0))

to get the matching city. This will work even if the equal values aren't in adjacent rows.

I don't know how many rows you have in your actual data, if it was more than 100 you'd need to use a smaller amount than .01.

Pleae note that these are Array Formulae and need to be entered with CtrlShiftEnter

For the smallest ones, just change LARGE to SMALL.

enter image description here

If you wanted to exclude any non-numeric values like "not ranked" you would need

=INDEX(D$2:D$9,MATCH(LARGE(IF((A$2:A$9="Restaurants")*ISNUMBER(C$2:C$9),C$2:C$9+ROW(C$2:C$9)/10^7),ROW(1:1)),C$2:C$9+ROW(C$2:C$9)/10^7,0))

I wouldn't recommend changing it to use D:D, C:C etc. because it would be slow, but it would work.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Thanks for this Tom. – pedroteixeira07 Oct 22 '16 at 20:02
  • I'm still around with my issue and can't seem to find a proper solution or I'm probably not excel savvy enough to see the answer. – pedroteixeira07 Oct 22 '16 at 20:03
  • OK I will add to my answer – Tom Sharpe Oct 22 '16 at 21:52
  • Tom, I always just use `+ROW(C$2:C$9)/10^7` to add increment for the tie-break – MacroMarc Oct 22 '16 at 22:06
  • Yes that's safer than .01 - will add it to my answer. – Tom Sharpe Oct 23 '16 at 08:08
  • Tom, many many thanks - this worked like a charm. Would never get to this point by myself. Just as a final note, I have yet another limitation, up until the limit of 4915 row the function works fine (A$2:A$4915). This is also the number of rows that the cell value is a number, the remaining ones are text chars. Thus, if I increase the range (A$2:A$8852) or ideally (A:A), I get a #VALUE! error. Is there any way around this to use this function but deal with chars as well? – pedroteixeira07 Oct 29 '16 at 18:54
  • I'd have to think about that one! Can you give me an idea what the text values look like plz? – Tom Sharpe Oct 30 '16 at 18:23
  • And are the text values considered to be 'higher' than the numeric ones? – Tom Sharpe Oct 30 '16 at 18:57
  • And are the text values in any particular order? – Tom Sharpe Oct 30 '16 at 21:04
  • The text values can only be 3: "not ranked" "dropped from top 30" and "new in top 30". Whereas the numbers range from -100 to 100. The text values are not relevant for the large/small function and can be left out for all that matters. They don't have any order as well within the 8852 list. – pedroteixeira07 Oct 30 '16 at 21:14
  • OK if you just want to exclude them then it shouldn't be too difficult. I will add it to my answer. – Tom Sharpe Oct 30 '16 at 21:34
  • Worked like a charm! Perfect. Can't thank you enough! And yes, will keep it limited to a number so it doesn't require so much processing. Many thanks again. – pedroteixeira07 Oct 30 '16 at 23:21