Find row of data

[royend]

Hi,

I have a worksheet containing a persons name and some facts about th
person. In another sheet I woul like to pick persons from a list (i
cell A7) and then have the extra information appear in the cell next t
the selected person (cell B7). With my setup, all I need is th
row-number of the person to make this work. What I tryed to pick u
the correct row-number is:

MATCH(A7,Names!A$1:A$150,ROW()) 'The ROW() is supposed to get th
correct row-number of the selected person.

It sometimes give me correct info, but most of all just gives me
random number (it seems).

All tips and hints will be appreciated.

Roy Endr

 

[Frank Kabel]

Hi
nearly there:
One way:
=VLOOKUP(A7,Names!A$1$150,2,0)
to get the value from column B

or using iNDEX and MATCH
=INDEX(Names!B$1:$B$150,MATCH(A7,Names!A$1:A$150,0))

 

[Max]

Perhaps try this example set-up which uses OFFSET and MATCH ..

Assume you have in Sheet1, in cols A to D, data from row2 down:

Name Fact1 Fact2 Fact3
ABC 9000 Text1 Data1
DEF 2000 Text2 Data2
XYZ 7000 Text3 Data3
etc

In Sheet2
-----------
Suppose you have in col A, data in A2 down as follows:

Name
XYZ
ABC
DEF
etc

Put in B2:

=IF(ISNA(MATCH(TRIM($A2),Sheet1!$A:$A,0)),"",OFFSET(Sheet1!$A$1,MATCH(TRIM($
A2),Sheet1!$A:$A,0)-1,COLUMN()-1))

Copy B2 across to D2, then copy down as many rows as there is data in col A

Cols B to D will extract the corresponding values from cols B to D of Sheet1
for matching names listed in col A

Non-matching names listed in col A will return blanks [""] in col B

TRIM() is used to improve robustness of matching, especially where
text-matching is involved, to remove any inadvertent leading, trailing or
in-between words extraneous spaces (usually these are not apparent /
visible) which may be present in the look-up values in col A of Sheet2

 

[royend]

WOW

Thanks for all your help Frank.
I am truly grateful.

Best Regards.
Roy Endr