Cross referencing with VLookup ?

[Guest]

I have a sheet with downloaded data:
col c with Name & col f with ID data as below:
C F
Smith 123
Jones 456

I have another sheet with the same info, different cols.
H L
Smith 123
Jones 321
I created another sheet that just has the above data referenced from the 2
sheets:

A B C
D
sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK
sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321)
Alert

I then want to cross-reference as follows: being that the ID#'s ( col B &
col d) for smith in row 1 match, then I'll have an OK in the E col.. but if
the #'s don't match, like in the jones row ( col B & col D), then I'll have
an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but
what formulas/functions do I need on sheet 3 to obtain the data from sheet 1
& 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a
Vlookup, but can't to get it to work.
I tried to have Vlookup return the name next to the ID, and if the same ID
from the other column didn't match it name, then the alert.

This probably doesn't make any sense, but hopefully.... Any help would be
greatly appreciated.

Thanks,

Steve

 

[Guest]

On sheet3:

Assuming data starts on row 1....

A1: =Sheet1!C1
B1: =Sheet1!F1
C1: =VLOOKUP(A1,sheet2!H:L,1,0)
D1: =VLOOKUP(A1,sheet2!H:L,5,0)


If there is a possibility data doesn't exist on sheet2 then:

C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1,sheet2!H:L,1,0))
D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1,sheet2!H:L,1,5))


is one way. Copy formulae down.

HTH

 

[Guest]

Typo ...

D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1,sheet2!H:L,5,0))

 

[Guest]

Thanks much, it's partially working. The problem I know have is that in the
name column, sheet 1 has a variety of types ( last name only, last name first
name, last name comma first name, last name comma first name middle initial,
etc.)
Sheet 2 has last names only, and therefore it only works if the names are an
exact match. Is there some way I could have only the last name show in the
cell, thereby getting the needed match ? Maybe something like 'to the right
until a space is detected'?

Thanks again,

Steve

 

[Guest]

Upon further review....
Virtually all the names in sheet a have a comma after the last name, so
could something be used to use everything to the left of the comma ?

Thanks again,

Steve

 

[Guest]

Try:

=LEFT(VLOOKUP(A1&"*",Sheet1!H:L,1,0),FIND(",",VLOOKUP(A1&"*",Sheet1!H:L,1,0))-1)

Sheet1 is the one with "mixed" names

 

[Guest]

.. and for column L data:

D1: =VLOOKUP($A1&"*",Sheet1!$H:$L,5,0)

 

[Guest]

....in C1 do you really need to do a lookup? Only to capture if data is missing?

Otherwise make C1=A1

 

[Guest]

Perfect - It's working great. Thanks for all your patience and assistance.

The next problem I've deleloped is that when there is more than one of the
same name, such as 5 Smiths, what it's doing is returning the ID data for the
first Smith for all the Smiths, thereby producing errors for the 4 other
smiths. I could manually deal with that, but if another formula could be
devised to ignore anytime it finds more than one of the same name, that would
save me some time.
As an example, here are the formulas I'm using in the particular cells:
In G 24:
=IF(ISNA(VLOOKUP(D4,'Data'!C:F,4,0)),"",(VLOOKUP(D24,'data'!C:F,4,0)))
In H24:
=IF(E24=G24,"","ID Alert")
In J24:
=IF(ISERROR(H24),"",(H24))
Like I said, it works great. But is there a way to supress " " the "ID
Alert" in cell H24 if row C of the data sheet has any duplicate names, such
as those 5 Smiths ?

Again, thanks so much.

Steve

 

[Guest]

Steve,
Slightly confused by reference to Column E as previous postings
have only used columns A to D.

I realised also (after posting my reply!) that "Smiths" (multiple occurences
of same surname) would cause a problem.

Can you send me a sample w/book so I can look at the data and then try to
offer a "best" solution?

toppers at REMOVETHISjohntopley.fsnet.co.uk

 

[Guest]

Ok, great. Thanks,

It's on it's way.

Steve,
Slightly confused by reference to Column E as previous postings
have only used columns A to D.

I realised also (after posting my reply!) that "Smiths" (multiple occurences
of same surname) would cause a problem.

Can you send me a sample w/book so I can look at the data and then try to
offer a "best" solution?

toppers at REMOVETHISjohntopley.fsnet.co.uk