Retrieve whole row of data

[manxman]

I want to sift for one cell in a row, then have the other cells retrieved
also. Say the data table is this:

A B C
1 110 8 7
2 130 11 8
3 90 5 6

In cell A20 I enter =small(A1:A3,1)
In cell A21 I enter =small(A1:A3,2)
In cell A22 I enter =small(A1:A3,3)

Here is the result I want.

A B C
20 90 5 6
21 110 8 7
22 130 11 8

So I know how to get column A, but how about the rest of the row?

Thanks in advance for any help.

 

[JLatham]

In B20:
=VLOOKUP($A20,$A$1:$C$3,2,0)
in C20:
=VLOOKUP($A20,$A$1:$C$3,3,0)
fill down.

Notice that the only difference between the two formulas is ,2, in one ,3,
in the other. Check Help for VLOOKUP() for more information, but that
parameter says which column from the lookup matrix to return values from when
a match is found in the first column.

 

[manxman]

Thanks for the help JLatham. I have used Vlookup extensively, but didn't
realize it could be used in that fashion. There is a problem in that the
real data table has duplicate values in Col A with unique values in Col B &
C. I have modified the illustration accordingly. Using Vlookup returns a
duplicate all the way across for Col A=110.

Modified data Desired result Result
with Vlookup

A B C A B C
A B C
1 110 8 7 20 90 5 6 20
90 5 6
2 130 11 8 21 110 8 7 21
110 8 7
3 90 5 6 22 110 4 4 22
110 8 7
4 110 4 4 23 130 11 8 23
130 11 8


So somehow we need to retrieve values for Col B & C that are unique to that
row.

 

[manxman]

Thanks for the help JLatham. I have used Vlookup extensively, but didn't
realize it could be used in that fashion. There is a problem in that the
real data table has duplicate values in Col A with unique values in Col B &
C. I have modified the illustration accordingly. Using Vlookup returns a
duplicate all the way across for Col A=110.

Modified data Desired result Result with Vlookup

A B C A B C A B C

1 110 8 7 20 90 5 6 20 90 5 6
2 130 11 8 21 110 8 7 21 110 8 7
3 90 5 6 22 110 4 4 22 110 8 7
4 110 4 4 23 130 11 8 23 130 11 8


So somehow we need to retrieve values for Col B & C that are unique to that
row.

 

[JLatham]

Yes, VLOOKUP() has that drawback - stops looking at the first match.
Probably going to have to switch to some creative use of match and index.
Let me think on it, meanwhile some really sharp worksheet function person may
step in with the solution while I'm still scratching my head.

 

[RR WILL]

I am certain someone will have a better solution for you at this site.
Perhaps temporarily if you can sort your data by column A ascending, then you
can change the range reference slightly before filling down thus deleting one
of the repeats each time............example for the
B20..=VLOOKUP($A20,A1:$C$3,2,0) and for
C20...=VLOOKUP($A20,A1:$C$3,3,0)..........Will

 

[Max]

One play to try ..

Assume source data in cols A to D (eg below), from row2 down, where the key
col = col B (110,90,130,..) whose values are to be autosorted in ascending
order with corresponding data in cols C and D following intact

1 110 8 7
2 130 11 8
3 90 5 6
4 110 4 4
etc

(I ignored col A which is just a serial numbering?)

In F2:
=IF(B2="","",B2+ROW()/10^10)
Leave F1 blank

In G2:
=IF(ROWS($1:1)>COUNT($F:$F),"",INDEX(B:B,MATCH(SMALL($F:$F,ROWS($1:1)),$F:$F,0)))
Copy G2 to I2. Select F2:I2, copy down to cover the max expected extent of
source data in cols B to D. Hide away col F. Cols G to I will return the
required results, all neatly bunched at the top.

 

[manxman]

Thanks Max, that does the trick. Now I'll study for awhile to try and
understand your solution, and then see if I can get it to work on the real
data.

 

[Max]

welcome, manxman.

 

[nikko]

Hi Max

I'm trying to look-up my source data and only return the multiple unique
values.
However the formulas i'm using are returning the multiple values (including
dupes)

INDEX(Closed YTD_1'!$A$1:$M$9908,SMALL(IF('Closed
YTD_1'!$A$1:$A$9908=$C$4,ROW('Closed YTD_1'!$A$1:$A$9908)),ROW(1:1)),11))

any idea how this can be fixed?