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

 

[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

.

 

[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