Repalce value

[hershel]

I have sheet1 that contains

A B C D E F
SCHWARTZ412BEDFORD 718-599-1113
EPSTEIN415BEDFORD 718-000-7328 656-642-7771
BRACH416BEDFORD 718-202-0373 957-781-4383


Then I have another sheet2 that
A B C D E F
SCHRITZER412BEDFORD 5/3/1949 F DEM
SCHWARTZ412BEDFORD 8/24/1972 M LND
SCHWARTZ412BEDFORD 9/29/1769 F DEM


What I need is a formula to find the value from sheet 1 a1 in sheet 2
row A then
If the value of Row E is “F” it should Copy the value from row F
to sheet 1 Cell E1 if the value from row E is “M” then it should
copy the Value in sheet 1 column F1

 

[MRT]

pls check Excel Help, keyword is "VLOOKUP".

HTH
--
MRT

I have sheet1 that contains

A B C D E F
SCHWARTZ412BEDFORD 718-599-1113
EPSTEIN415BEDFORD 718-000-7328 656-642-7771
BRACH416BEDFORD 718-202-0373 957-781-4383


Then I have another sheet2 that
A B C D E F
SCHRITZER412BEDFORD 5/3/1949 F DEM
SCHWARTZ412BEDFORD 8/24/1972 M LND
SCHWARTZ412BEDFORD 9/29/1769 F DEM


What I need is a formula to find the value from sheet 1 a1 in sheet 2
row A then
If the value of Row E is “F” it should Copy the value from row F
to sheet 1 Cell E1 if the value from row E is “M” then it should
copy the Value in sheet 1 column F1

 

[p45cal]

It may not as easy as that, Vlookup will return only 1 result. In th
example cited, there are 2 Schwarzs in Sheet 2, one F and 1 M. I suspec
that the OP wants to see both results on a single row in sheet 1.

I'm having a play at the moment with the likes of

Code
-------------------
=INDEX(Sheet2!$F$1:$F$3,LARGE((Sheet2!A1:A3=Sheet1!A1)*(Sheet2!E1:E3="F")*ROW(Sheet2!A1:A3),1))

-------------------
in E1 an
Code
-------------------
=INDEX(Sheet2!$F$1:$F$3,LARGE((Sheet2!A1:A3=Sheet1!A1)*(Sheet2!E1:E3="M")*ROW(Sheet2!A1:A3),1)
-------------------
for F1, ARRAY ENTERED and copied down, which works except for wher
there are no matches whereupon index(array,0) returns the whole column.
I used Large to cope with more than one matching row in sheet 2.

I may have to rethink...

 

[ryguy7272]

This function will find multiple matches and return all
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

It may not as easy as that, Vlookup will return only 1 result. In the
example cited, there are 2 Schwarzs in Sheet 2, one F and 1 M. I suspect
that the OP wants to see both results on a single row in sheet 1.

I'm having a play at the moment with the likes of

Code:
--------------------
=INDEX(Sheet2!$F$1:$F$3,LARGE((Sheet2!A1:A3=Sheet1!A1)*(Sheet2!E1:E3="F")*ROW(Sheet2!A1:A3),1))

--------------------
in E1 and
Code:
--------------------
=INDEX(Sheet2!$F$1:$F$3,LARGE((Sheet2!A1:A3=Sheet1!A1)*(Sheet2!E1:E3="M")*ROW(Sheet2!A1:A3),1))
--------------------
for F1, ARRAY ENTERED and copied down, which works except for where
there are no matches whereupon index(array,0) returns the whole column.
I used Large to cope with more than one matching row in sheet 2.

I may have to rethink...

pls check Excel Help, keyword is "VLOOKUP".

HTH
--
MRT

I have sheet1 that contains

A B C D E F
SCHWARTZ412BEDFORD 718-599-1113
EPSTEIN415BEDFORD 718-000-7328 656-642-7771
BRACH416BEDFORD 718-202-0373 957-781-4383


Then I have another sheet2 that
A B C D E F
SCHRITZER412BEDFORD 5/3/1949 F DEM
SCHWARTZ412BEDFORD 8/24/1972 M LND
SCHWARTZ412BEDFORD 9/29/1769 F DEM


What I need is a formula to find the value from sheet 1 a1 in sheet 2
row A then
If the value of Row E is “F†it should Copy the value from row F
to sheet 1 Cell E1 if the value from row E is “M†then it should
copy the Value in sheet 1 column F1

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=156380

Microsoft Office Help

.

 

[p45cal]

Got it working.
For the Fs; In any spare cell in row 1 of sheet1, ARRAY-ENTERED, the
copied down:

Code
-------------------
=INDEX(Sheet2!$F$1:$F$3,LARGE(IF(ISERROR((IF(Sheet2!$A$1:$A$3=Sheet1!$A1,1,""))*(IF(Sheet2!$E$1:$E$3="F",1,""))*ROW(Sheet2!$A$1:$A$3)),"",(IF(Sheet2!$A$1:$A$3=Sheet1!$A1,1,""))*(IF(Sheet2!$E$1:$E$3="F",1,""))*ROW(Sheet2!$A$1:$A$3)),1)
-------------------

For the Ms; In any spare cell in row 1 of sheet1, ARRAY-ENTERED the
copied down:

Code
-------------------
=INDEX(Sheet2!$F$1:$F$3,LARGE(IF(ISERROR((IF(Sheet2!$A$1:$A$3=Sheet1!$A1,1,""))*(IF(Sheet2!$E$1:$E$3="M",1,""))*ROW(Sheet2!$A$1:$A$3)),"",(IF(Sheet2!$A$1:$A$3=Sheet1!$A1,1,""))*(IF(Sheet2!$E$1:$E$3="M",1,""))*ROW(Sheet2!$A$1:$A$3)),1)
-------------------


This works for the data you provided put into the top left corner o
the two sheets. It returns an error if there's no match - you'll need t
manually scrub those using F5 (Goto) and choose Formulas|Errors an
delete them. The rest you can copy/paste|values in situ.

I feel sure there must be something more elegant. (I'd have done thi
by a macro.

 

[MRT]

force to use VLOOKUP ...

Sheet1!E1:
{=IF(ISNA(VLOOKUP(A1&"M",Sheet2!$A$1:$A$3&Sheet2!$E$1:$E$3,1,0)),"","M")}
Sheet1!F1:
{=IF(ISNA(VLOOKUP(A1&"F",Sheet2!$A$1:$A$3&Sheet2!$E$1:$E$3,1,0)),"","F")}

and copy down

 

[p45cal]

The OP asked for:
"If the value of Row-(sic)- E is “F” it should Copy the value fro
row-(sic)- _F_ to sheet 1 Cell E1"
So the OP is expecting the likes of 'LND' or 'DEM'.
These formulae return only "F" and "M" from the formulae themselves.

..actually, the OP probably isn't expecting anything at all since h
seems to have lost interest.