Retrieve whole row of data
"manxman" wrote:
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" wrote:
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" wrote:
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.
|