Is there such thing as a VLOOKUP that recognises r

问题:

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.

回答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))

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.

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.