Lookup Function
 

I used either the vlookup, hlookup or lookup function a long time ago but I
not sure how I used it.

I need to compare the contents found on column B with the contents found on
column A to see if there are duplicates

If the value on B2 is found anywhere on A1:A5 I need it to tell me that
there is a duplicate.

Can you help?
Thanks


Ademar Nunes

Hi!

There are many ways to do this. Here are a few:

Entered as an array, will return TRUE for duplicate, FALSE
for no duplicate:

=OR(A1:A5=B2)

Also entered as an array:

=IF(OR(A1:A5=B2),"duplicate","")

Entered normally:

=IF(ISNUMBER(INDEX(A1:A5,MATCH
(B2,A1:A5,0))),"duplicate","")

Biff

-----Original Message-----
I used either the vlookup, hlookup or lookup function a
long time ago but I
not sure how I used it.

I need to compare the contents found on column B with the
contents found on
column A to see if there are duplicates

If the value on B2 is found anywhere on A1:A5 I need it
to tell me that
there is a duplicate.

Can you help?
Thanks


Ademar Nunes


.

you might also take a look at Chip Pearson's page
Duplicates
http://www.cpearson.com/excel/duplic...gingDuplicates

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Biff" wrote in message ...
Hi!

There are many ways to do this. Here are a few:

Entered as an array, will return TRUE for duplicate, FALSE
for no duplicate:

=OR(A1:A5=B2)

Also entered as an array:

=IF(OR(A1:A5=B2),"duplicate","")

Entered normally:

=IF(ISNUMBER(INDEX(A1:A5,MATCH
(B2,A1:A5,0))),"duplicate","")

Biff

-----Original Message-----
I used either the vlookup, hlookup or lookup function a
long time ago but I
not sure how I used it.

I need to compare the contents found on column B with the
contents found on
column A to see if there are duplicates

If the value on B2 is found anywhere on A1:A5 I need it
to tell me that
there is a duplicate.

Can you help?
Thanks


Ademar Nunes


.

Thanks guys, those solutions work great.
It would be even better if we could do this:

ColumnA ColumnB ColumnC ColumnD
Tom 30 Jerry 25
Jerry 25 Jim 17
Jim 17
Jerry 25
Jim 17

Columns C and D are the columns where I'd plug in the formulas. The formula
in ColumnC would lookup columnA and return only distinct values into Column
C, thus ignoring duplicates. The formula in CulumnD would return the
corresponding age, found in Column B for that distinct value.

Can you help again?
--
Thanks,

Ademar Nunes

"David McRitchie" wrote in message
...
you might also take a look at Chip Pearson's page
Duplicates
http://www.cpearson.com/excel/duplic...gingDuplicates

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Biff" wrote in message
...
Hi!

There are many ways to do this. Here are a few:

Entered as an array, will return TRUE for duplicate, FALSE
for no duplicate:

=OR(A1:A5=B2)

Also entered as an array:

=IF(OR(A1:A5=B2),"duplicate","")

Entered normally:

=IF(ISNUMBER(INDEX(A1:A5,MATCH
(B2,A1:A5,0))),"duplicate","")

Biff

-----Original Message-----
I used either the vlookup, hlookup or lookup function a
long time ago but I
not sure how I used it.

I need to compare the contents found on column B with the
contents found on
column A to see if there are duplicates

If the value on B2 is found anywhere on A1:A5 I need it
to tell me that
there is a duplicate.

Can you help?
Thanks


Ademar Nunes


.

Let A2:B7 house the sample you provided:

{"Name","Age";"Tom",30;"Jerry",25;"Jim",17;"Jerry" ,25;"Jim",17}

C1 must house a 0.

C2: Count

C3, copied down:

=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99 999999999999E+307,$C$1:C1)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,$C$3:$C$7)

which calculates the number of unique records.

D2: D-Name

E3: Age

D3, copied across then down:

=IF(ROW()-ROW(D$3)+1<=$D$1,INDEX(A$3:A$7,MATCH(ROW()-ROW(D$3)+1,$C$3:$C$7)),"")

Ademar Wrote:
Thanks guys, those solutions work great.
It would be even better if we could do this:

ColumnA ColumnB ColumnC ColumnD
Tom 30 Jerry 25
Jerry 25 Jim 17
Jim 17
Jerry 25
Jim 17

Columns C and D are the columns where I'd plug in the formulas. The
formula
in ColumnC would lookup columnA and return only distinct values into
Column
C, thus ignoring duplicates. The formula in CulumnD would return the
corresponding age, found in Column B for that distinct value.

Can you help again?
--
Thanks,

Ademar Nunes

[...]


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274755

This is a bit advance for me and I don't quite understand. What formula do
I copy in columns C and D?

Thanks Aladin,

Ademar Nunes

"Aladin Akyurek" wrote in
message ...

Let A2:B7 house the sample you provided:

{"Name","Age";"Tom",30;"Jerry",25;"Jim",17;"Jerry" ,25;"Jim",17}

C1 must house a 0.

C2: Count

C3, copied down:


=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99 999999999999E+307,$C$1:C1)
+1,"")

D1:

=LOOKUP(9.99999999999999E+307,$C$3:$C$7)

which calculates the number of unique records.

D2: D-Name

E3: Age

D3, copied across then down:


=IF(ROW()-ROW(D$3)+1<=$D$1,INDEX(A$3:A$7,MATCH(ROW()-ROW(D$3)+1,$C$3:$C$7)),
"")

Ademar Wrote:
Thanks guys, those solutions work great.
It would be even better if we could do this:

ColumnA ColumnB ColumnC ColumnD
Tom 30 Jerry 25
Jerry 25 Jim 17
Jim 17
Jerry 25
Jim 17

Columns C and D are the columns where I'd plug in the formulas. The
formula
in ColumnC would lookup columnA and return only distinct values into
Column
C, thus ignoring duplicates. The formula in CulumnD would return the
corresponding age, found in Column B for that distinct value.

Can you help again?
--
Thanks,

Ademar Nunes

[...]


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274755

I plugged in the formula but it is not working properly. It kind of works.

--
Regards,

Ademar Nunes

"Aladin Akyurek" wrote in
message ...

Let A2:B7 house the sample you provided:

{"Name","Age";"Tom",30;"Jerry",25;"Jim",17;"Jerry" ,25;"Jim",17}

C1 must house a 0.

C2: Count

C3, copied down:


=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99 999999999999E+307,$C$1:C1)
+1,"")

D1:

=LOOKUP(9.99999999999999E+307,$C$3:$C$7)

which calculates the number of unique records.

D2: D-Name

E3: Age

D3, copied across then down:


=IF(ROW()-ROW(D$3)+1<=$D$1,INDEX(A$3:A$7,MATCH(ROW()-ROW(D$3)+1,$C$3:$C$7)),
"")

Ademar Wrote:
Thanks guys, those solutions work great.
It would be even better if we could do this:

ColumnA ColumnB ColumnC ColumnD
Tom 30 Jerry 25
Jerry 25 Jim 17
Jim 17
Jerry 25
Jim 17

Columns C and D are the columns where I'd plug in the formulas. The
formula
in ColumnC would lookup columnA and return only distinct values into
Column
C, thus ignoring duplicates. The formula in CulumnD would return the
corresponding age, found in Column B for that distinct value.

Can you help again?
--
Thanks,

Ademar Nunes

[...]


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274755

Change the formula in C3 to:

=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99 999999999999E+307,$C$1:C2)+1,"")

Ademar Wrote:
I plugged in the formula but it is not working properly. It kind of
works.

--
Regards,

Ademar Nunes

"Aladin Akyurek" wrote
in
message ...

Let A2:B7 house the sample you provided:

{"Name","Age";"Tom",30;"Jerry",25;"Jim",17;"Jerry" ,25;"Jim",17}

C1 must house a 0.

C2: Count

C3, copied down:


=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99 999999999999E+307,$C$1:C1)
+1,"")

D1:

=LOOKUP(9.99999999999999E+307,$C$3:$C$7)

which calculates the number of unique records.

D2: D-Name

E3: Age

D3, copied across then down:


=IF(ROW()-ROW(D$3)+1<=$D$1,INDEX(A$3:A$7,MATCH(ROW()-ROW(D$3)+1,$C$3:$C$7)),
"")

Ademar Wrote:
Thanks guys, those solutions work great.
It would be even better if we could do this:

ColumnA ColumnB ColumnC ColumnD
Tom 30 Jerry 25
Jerry 25 Jim 17
Jim 17
Jerry 25
Jim 17

Columns C and D are the columns where I'd plug in the formulas.
The
formula
in ColumnC would lookup columnA and return only distinct values
into
Column
C, thus ignoring duplicates. The formula in CulumnD would return
the
corresponding age, found in Column B for that distinct value.

Can you help again?
--
Thanks,

Ademar Nunes

[...]


--
Aladin Akyurek

------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread:
http://www.excelforum.com/showthread...hreadid=274755



--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274755