VLOOKUP With Duplicate Values-CodePudding

I have 2 sheets like the following:

Sheet 1

Sheet 2

I want to enter the values from column B on Sheet 2 into column B on Sheet 1. In Sheet 1 there are some double/duplicate data such as:

Aek Godang, dan Aek Kanopan

so when I use the formulas:

=VLOOKUP(A1,$Sheet2.A1:B15,2,1) or =INDEX($Sheet2.A1:B15, MATCH(A1,$Sheet2.A1:A15,0),2)

on row 14 where there is the same data as the previous row, it will always be an error

how to solve it or the formula so that the result can be like this:

CodePudding user response：

in B1 use:

=INDEX(IFNA(VLOOKUP(A1:A; Sheet2!A1:B; 2; )))

CodePudding user response：

You need only this, enter the formula in cell B1 of sheet 1 and remember to press cse as its an array formula(for excel 2010 till 2019).

=SMALL(IF(A1='Sheet2'!A$1:A$15,ROW('Sheet2'!A$1:A$15)),1)

=INDEX('Sheet2'!B$1:B$15,SMALL(IF(A1='Sheet2'!A$1:A$15,ROW('Sheet2'!A$1:A$15)),1))

The first link given in the comment with countif doesn't work. Aggregate Function is not available in Google Sheet. In Libre office Small function does work.

With COUNTIF error will be showing, wrong way to resolve.