Using v lookup but getting the same value due to same range issue

[ROHAN999]

I have these value in column a and b of my sheet 1

employee rocord
a 1
b 3
d 4
a 6

in sheet 2 employee are arranged and using vlookup

result is

employ record
a 1
a 1
b 3
d 4
a 1


formula using:=vlookup(sheet2!a2,sheet1!a$2:b$5,2,0)

how to solve this issue: Kindly guide other formula by which the result coulb be:

Employ record
a 1
a 6
b 3
d 4
a n/a




kindly assist

[ROHAN999]

sry in arrangment it shd be like

in sheet 2 employee are arranged and using vlookup

result is

employ record
a 1
a 1
a 1
b 3
d 4



formula using:=vlookup(sheet2!a2,sheet1!a$2:b$5,2,0)

how to solve this issue: Kindly guide other formula by which the result coulb be:

Employ record
a 1
a 6
a n/a
b 3
d 4

[Glenn Kennedy]

Using this array formula:
=INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$5=Sheet2!A2,ROW(Sheet1!$A$2:$A$5)),COUNTIF(Sheet2!$A$2:A2,Sheet2!A2)))

assuming all data start on row 2...

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Glenn Kennedy]

Of course, if you want rid of the error message at the last "a", use:

=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$5=Sheet2!A2,ROW(Sheet1!$A$2:$A$5)),COUNTIF(Sheet2!$A$2:A2,Sheet2!A2))),"")

and the formula will return a blank.

[ROHAN999]

very nice -thank you

[Glenn Kennedy]

You're welcome, and thanks for the Rep.