Vlookup - find value 3,4,5 etc..

Solved/Closed
PM - Jul 29, 2009 at 06:08 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 30, 2009 at 06:36 AM
Hello,

I can use a vlookup to find the first and second value using True or False after the formula. However i would like to able to find more values if column A contains more then 2 duplicate values.

Example:

Column A Column B
1 10
1 20
2 15
1 30
1 40
2 30


=VLOOKUP(A2,Sheet1!$A$2:$B$92,2,FALSE) This will show 10
=VLOOKUP(A2,Sheet1!$A$2:$B$92,2,TRUE) This will show 40

How do i get all the other values??

3 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 29, 2009 at 08:56 PM
in an empty cell (not in columns A or B) copy paste this long formula

=IF(COUNTIF($A$1:$A$1000,$A$2)>=ROWS($1:1),INDEX($B$1:$B$1000,SMALL(IF($A$1:$A$1000=$A$2,ROW($1:$1000)),ROW(1:1))),"")

INVOKE THIS FORMULA BY HITTING CONTROL+SHIFT+ENTER

copy this formula to the next few cells down till you get a blank.

you will get like this

10
20
30
40
this formula is courtesy Frank Gable.
2
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 30, 2009 at 06:36 AM
I want a clarification
My results are vertical
you want it horizontal

if it is done in the same sheet you can always copy paste it to another sheet. The formula will be tittle more less complex. for horizonal configuration

one way is jut to copy and pastespecial -transpose in another cell
or you can use this formula

suppose 1 is entered in E1
in F1 copy paste this formula
=IF(COUNTIF($A$1:$A$1000,$E$1)>=ROWS($1:1),INDEX($B$1:$B$1000,SMALL(IF($A$1:$A$1000=$E$1,ROW($1:$1000)),COLUMN(A1))),"")

invoke this formula with contorl+shift+enter
copy F1 to G1,H1,I1 etc till you get an error value #NUM!

the result will be
1 10 20 30 40 #NUM!

You can copy paste anywhere any sheet.

It is the same formula of Frank Gable sightly modified
2
Hi venkat1926,

thanks for your help...and also thanks to Frank Gable.

I dont think this formula is working correctly, maybe i didnt explain what i need done properly.

With regards to the data i sent in my first message, i need it to display all the values horizontal on another sheet.

Example:(Sheet2)

Column A contains the data im searching from sheet 1.
Column B,C,D shows the results off sheet 1 which relate to column A.

(Sheet2)
Column A, Column B, Cloumn C, Column D
1 10 20 30

Thanks

PM
1