# Vlook up with Unique Id Returning Multiple values,show multiple rows based on unique ID

1. ## Vlook up with Unique Id Returning Multiple values,show multiple rows based on unique ID

Hi, everyone,

I have a set of values as shown below in sheet1. All I need to do is I need to retrieve this info in sheet 2 when I enter the ID as shown below. I have tried with vloopup formula, but I am able to get only one value which is on the top and not the rest, please help me to do it.

sheet1
---------------------------------------------
ID Email Status Need Renewal
0001 a@gmail.com Active No
0001 b@gmail.com Active No
0001 c@gmail.com Active No
0001 d@gmail.com Inactive Yes
0002 a@gmail.com Inactive Yes
0002 b@gmail.com Inactive Yes
0002 c@gmail.com Inactive Yes
0003 a@gmail.com Active No
0004 a@gmail.com Inactive Yes
0004 b@gmail.com Active No

-------------------------------------------

sheet2
-----------------------------------------
Enter ID= 0001

Details

ID Email Status Need Renewal
----------------------------------------------
0001 a@gmail.com Active No
0001 b@gmail.com Active No
0001 c@gmail.com Active No
0001 d@gmail.com Inactive Yes
---------------------------------------------

2. ## Re: Vlook up with Unique Id Returning Multiple values,show multiple rows based on unique I

hi nuthan, welcome to the forum. you may try this array formula in the Sheet2 if ID is in B1:
=IFERROR(INDEX(Sheet1!A\$2:A\$11,SMALL(IF(Sheet1!\$A\$2:\$A\$11=\$B\$1,ROW(Sheet1!\$B\$2:\$B\$11)-ROW(Sheet1!\$B\$2)+1),ROWS(A\$3:A3))),"")

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it

3. ## Re: Vlook up with Unique Id Returning Multiple values,show multiple rows based on unique I

Thank you Ben, It is working fine, Thank you for your effort once again.

There are currently 1 users browsing this thread. (0 members and 1 guests)