CODEWITHSUNDEEP
excelgoogle-sheetsvlookuplookuplibreoffice-calc
Andhi Irawan
Andhi Irawan

Reputation: 481

VLOOKUP With Duplicate Values

I have 2 sheets like the following:

enter image description here

Sheet 1

enter image description here

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

enter image description here

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

enter image description here

Upvotes: 0

Views: 2309

Answers (2)

player0
player0

Reputation: 1

within GS in B1 use:

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

Upvotes: 2

user18680439
user18680439

Reputation:

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)

enter image description here

=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.

enter image description here

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

Upvotes: -1

Related Questions