Formula help

[ljsimm]

Sheet1 has a name in A1 & a number in B1, name in A2 number in B2 and s
on (numbers will continually be changed). On sheet2 I used =a or =
and so on (the results of =a or =b are the names from sheet 1). Wha
I want to accomplish is every time I type =a or =b and the name appear
I also want the result from sheet1 B1 and so on to appear in the cel
directly below. Not very experienced just learning. Looking fo
help

 

[Yoyr Message is a]

Hi Ljsimm
You post is a bit vague, but if you mean you want B1 on
sheet 2 to match up with the name on Sheet 1.
Try:
=IF(A1=Sheet1!A1,Sheet1!B1,"")
Which means if the there is no match you will have a blank
cell.
If the order of the names is going to change on Sheet1 a
VLOOKUP might be a better idea.
Regards
Michael

 

[Max]

Another option:

In Sheet1
-----------
Assume names are in col A, numbers in col B, other data in col C
starting in row1 down, e.g.:

Jack 123 data1
Max 456 data2
etc

Note: Names in col A are assumed unique

In Sheet2
-----------
Assuming a name will be entered in A1

Put in A2:

=OFFSET(Sheet1!$A$1,MATCH(TRIM($A1),Sheet1!$A:$A,0)-1,COLUMN())

Copy A2 across to B2

A2 and B2 will return
the corresponding number and other data from cols B and C of Sheet1
for the name entered in A1
---

To cater for the possibility of unmatched names entered in A1 of Sheet2,

Put instead in A2 of Sheet2:

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

Copy A2 across to B2, as before

Unmatched names entered in A1 will now return blanks [""] instead of #NA

 

[CLR]

Maybe put this in the C2..........assuming your selected name is in C1

=VLOOKUP(C1,Sheet1!A:B,2,FALSE)

Vaya con Dios,
Chuck, CABGx3