Clever Geek Handbook

Excel Lookup Returns Unique Value

I have two sheets:

Item   ID
Box    78
Glue   105
Box    85

Another sheet

Item    ID
Box
Box
Glue

On the second sheet, I want to find a ID

from the first sheet.

Where I am duplicating (as in Box

) I want to return ID

one at a time. Thus, the search Box

will return 78

the first time and the 85

second time.

Like this:

Item    ID
Box     78
Box     85
Glue    105
+3


source to share


4 answers


Alternative solution. Requires no helper columns, no array input. In cell B2 of sheet 2 and copy:



=INDEX(Sheet1!$B$2:$B$4,MATCH(1,INDEX((Sheet1!$A$2:$A$4=A2)*(COUNTIF(B$1:B1,Sheet1!$B$2:$B$4)=0),),0))
+1


source


You can use a little change to INDEX / MATCH:

 =INDEX(B$2:B$4,SMALL(IF(A$2:A$4=C2,ROW(B$2:B$4)-1,""),COUNTIF(C$2:C2,C2)))

Validate as array formula with Ctrl + Shift + Enter, then autocomplete down.

Explanation of cell references:



A $ 2: A $ 4 - Item Column (sheet1)

B $ 2: B $ 4 - identification column (sheet 1)

C $ 2: C2 - position column (sheet2)

C2 - The item you are looking for

0


source


At first glance, this seems impossible because it VLOOKUP

doesn't know how to handle multiple Box

es.

But using this ingenious answer , you can actually make the job.

On sheet 1, enter the formulas:

Some formulae for sheet 1

Result:

item    instance    combined    id  range_above
Box     1           Box1        78  $A$2:$A$2
Glue    1           Glue1       105 $A$2:$A$3
Box     2           Box2        85  $A$2:$A$4

Now you can VLOOKUP

turn off combined

instead of off item

. (Obviously, you will have to do the same trick in sheet 2.)

Note that all of these formulas can simply be extended, but your table is long. It will always work.

0


source


A good way to do this would be to use an array formula to find the values ​​from the first sheet using the number of times each item is repeated from the second sheet. This method does not require "helper" columns.

The formula that will accomplish this: {=INDEX(Sheet1!A:B,SMALL(IF((Sheet1!A:A)=Sheet2!A2,ROW(Sheet1!A:A),""),COUNTIF($A$2:$A2,"="&$A2)),2)}

This formula is an array formula. You can copy it to cell B2

on Sheet2

and press Ctrl+ Shift+ Enterto enter it. You have to press Ctrl+ Shift+ Enterto create an array formula. You can drag this formula down in column B as many rows as you like.

Your worksheet, when completed, will contain the following formulas: Finished Worksheet

Let's break this formula down into its components:

  • Determine how many times a given element has been repeated in column A. We do this with the following code, which will count the number of times the current row element has appeared in all previous rows in column A. An absolute reference for the beginning of the range and a relative reference for the end ensures that that the range will automatically update as we drag the formula down in column B:

    COUNTIF($A$2:$A2,"="&$A2)

  • Determine the kth row in Sheet1 containing the item in column A of sheet 2. The following code first uses an operator IF

    to find all rows in Sheet1 that contain the item in column A of sheet 2. This uses array syntax to compare all rows in a specified range with a value ... The operator IF

    returns an array containing the strings that were identified, if found. If none are found, it returns an empty string.

    IF((Sheet1!A:A)=Sheet2!A2,ROW(Sheet1!A:A),"")

  • Look at the value of the nth row, which was identified by a function SMALL

    that picks the nth smallest element from the array. For k

    we are passing the score that was calculated in step 1.

    SMALL(IF((Sheet1!A:A)=Sheet2!A2,ROW(Sheet1!A:A),""),COUNTIF($A$2:$A2,"="&$A2))

  • Finally, we bundle everything together into a function INDEX

    that will look for a value in the specified row (identified in step 3) and column (2) in the specified range.

    {=INDEX(Sheet1!A:B,SMALL(IF((Sheet1!A:A)=Sheet2!A2,ROW(Sheet1!A:A),""),COUNTIF($A$2:$A2,"="&$A2)),2)}
0


source






More articles:


All Articles