Repalce value

hershel

unread,

Nov 22, 2009, 9:19:06 PM11/22/09

to

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

unread,

Nov 23, 2009, 4:56:02 AM11/23/09

to

pls check Excel Help, keyword is "VLOOKUP".

HTH
--
MRT

"hershel" <hershel...@gmail.com> wrote in message news:bf7b1a32-2302-4a26...@x16g2000vbk.googlegroups.com...

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

p45cal

unread,

Nov 23, 2009, 6:02:29 AM11/23/09

to

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...

MRT;566656 Wrote:
> pls check Excel Help, keyword is "VLOOKUP".
>
> HTH
> --
> MRT
>
> "hershel" <hershel...@gmail.com> wrote in message
> news:bf7b1a32-2302-4a26...@x16g2000vbk.googlegroups.com...
> 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

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

ryguy7272

unread,

Nov 23, 2009, 1:09:01 PM11/23/09

to

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''.

"p45cal" wrote:

> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>

p45cal

unread,

Nov 23, 2009, 2:45:48 PM11/23/09

to

Got it working.
For the Fs; In any spare cell in row 1 of sheet1, ARRAY-ENTERED, then
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 then
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 of
the two sheets. It returns an error if there's no match - you'll need to
manually scrub those using F5 (Goto) and choose Formulas|Errors and
delete them. The rest you can copy/paste|values in situ.

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

--
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

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

MRT

unread,

Nov 25, 2009, 6:04:13 AM11/25/09

to

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" <p45cal...@thecodecage.com> wrote in message news:p45cal...@thecodecage.com...

p45cal

unread,

Nov 25, 2009, 6:57:29 AM11/25/09

to

The OP asked for:
"If the value of Row-(sic)- E is �F� it should Copy the value from
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 he
seems to have lost interest.