Vlookup - find value 3,4,5 etc..
Solved/Closed
PM
-
Jul 29, 2009 at 06:08 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 30, 2009 at 06:36 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 30, 2009 at 06:36 AM
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 29, 2009 at 08:56 PM
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.
=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.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 30, 2009 at 06:36 AM
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
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
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
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
