look up a value in one column to another colum

[Oscar Kelley - Salmon Days Festival]

I am trying to check it a item is new. Comparing two columns (900-1500 records)

I using IF(ISNA(LOOKUP(A2,G$2:g$1474)),"","Match")

But match is not being returned for any records
sample of data

Isomedia MAC# Unit ServiceStart Match Div_Lot RegNumber MAC
1023201471 050-0026 1/20/04 050-0026 1023201471 0002A11B2730
1037653266 025-016R 5/25/04 025-016R 1037653266 0002A1188B60
1045954143 0002A11BC470 007-0019 6/11/04 007-0019 1045954143 0002A11BC470
1048849824 0002A119D1F0 051-0004 12/8/04 051-0004 1048849824 0002A119D1F0
1055082119 025-091R 6/9/04 025-091R 1055082119 0002A1192DA0



--
Oscar Kelley
HFN Data Analyst
Highlands Fiber Network

[Max]

> I using IF(ISNA(LOOKUP(A2,G$2:g$1474)),"","Match")

Try instead in say, K2:
=ISNUMBER(MATCH(A2,G:G,0))
Copy K2 down to the last row of data in col A

Col K will return TRUE if the item in col A is found in col G, FALSE
otherwise. Then we could just do a Data > Filter > Autofilter on col K to
filter out FALSE ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Oscar Kelley - Salmon Days Festival" wrote:
> I am trying to check it a item is new. Comparing two columns (900-1500 records)
>
> I using IF(ISNA(LOOKUP(A2,G$2:g$1474)),"","Match")
>
> But match is not being returned for any records
> sample of data
>
> Isomedia MAC# Unit ServiceStart Match Div_Lot RegNumber MAC
> 1023201471 050-0026 1/20/04 050-0026 1023201471 0002A11B2730
> 1037653266 025-016R 5/25/04 025-016R 1037653266 0002A1188B60
> 1045954143 0002A11BC470 007-0019 6/11/04 007-0019 1045954143 0002A11BC470
> 1048849824 0002A119D1F0 051-0004 12/8/04 051-0004 1048849824 0002A119D1F0
> 1055082119 025-091R 6/9/04 025-091R 1055082119 0002A1192DA0
>
>
>
> --
> Oscar Kelley
> HFN Data Analyst
> Highlands Fiber Network
>

[exxon99]

I am doing the same function but mine needs to return a value. what agonizes me is that the vlookup function will work only for certain rows of data but then it does not retrieve value for the other and return N/A. Why does this happen and is there a better function to use instead of Vlookup?

I have 3 columns of Student Name, ID and Marks. The information is copied from another system and is pasted into excel. I then lookup the Student ID from a existing report template (which has all the student's name and ID) in one sheet and match it with the ID pasted in the second sheet. It would then return the Marks. Because not all the students would have sat fr the exam. Ironically the function is able to retrieve some IDs and Marks but the rest is left as N/A eventhough their names has been recorded with marks.

I hope you understand what i mean and be able to help out. Thanks

[Max]

"exxon99" wrote:
> I am doing the same function but mine needs to return a value. what
> agonizes me is that the vlookup function will work only for certain
> rows of data but then it does not retrieve value for the other and
> return N/A. Why does this happen and is there a better function to use
> instead of Vlookup?

It's probably more due to inconsistencies in the data, viz. between the
lookup values and the values in the lookup col, rather than in the choice of
function.
For example, the lookup col values may be text numbers while lookup values
are probably real numbers (or it could be the other way around). Some ways of
handling these situations to yield more robust matching are provided below.

> I have 3 columns of Student Name, ID and Marks. The information is
> copied from another system and is pasted into excel. I then lookup the
> Student ID from a existing report template (which has all the student's
> name and ID) in one sheet and match it with the ID pasted in the second
> sheet. It would then return the Marks. Because not all the students
> would have sat fr the exam. Ironically the function is able to retrieve
> some IDs and Marks but the rest is left as N/A eventhough their names
> has been recorded with marks.

Assuming the "3 columns of Student Name, ID and Marks"
are in sheet: X, within cols A to C, data from row2 down
(Student IDs in B2 down are assumed text numbers
in format "0000", viz. 4 digit numbers with leading zeros)

... and the "existing report template" is in sheet: Y (say)
with the students' IDs in B2 down (assumed to be real numbers),
we could try in Y's C2:
=INDEX(X!C:C,MATCH(TEXT(B2,"0000"),X!B:B,0))
and copy C2 down

Y's col C should retrieve the marks from col C in X for the IDs listed in
col B

(The TEXT function will convert the real numbers in col B to text numbers
for consistency & more robust matching)

If it's the other way around, i.e. the lookup col values in X are real
numbers and the lookup values in Y are text numbers, then try instead in Y's
C2, copied down:
=INDEX(X!C:C,MATCH(B2+0,X!B:B,0))

(Adding zero to the text numbers in col B will coerce these to real numbers
w/o affecting their intrinsic values, and provide enhanced matching with the
real numbers in the lookup col in X)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Oscar Kelley - Salmon Days Festival]

Thank it didn't work. ???? I even made sure both columns were formatted the
same.

Column A has 1241 records, column G 1474 - 233 increase. I trying to match
the ones that are the same.

What happened when I changed it to ISNUMBER, all but 3 were FALSE, whereas
it should be closer to 1241, except for those that are new or terminated.
--
Oscar Kelley
HFN Data Administrator
Highlands Fiber Network



"Max" wrote:

> > I using IF(ISNA(LOOKUP(A2,G$2:g$1474)),"","Match")
>
> Try instead in say, K2:
> =ISNUMBER(MATCH(A2,G:G,0))
> Copy K2 down to the last row of data in col A
>
> Col K will return TRUE if the item in col A is found in col G, FALSE
> otherwise. Then we could just do a Data > Filter > Autofilter on col K to
> filter out FALSE ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Oscar Kelley - Salmon Days Festival" wrote:
> > I am trying to check it a item is new. Comparing two columns (900-1500 records)
> >
> > I using IF(ISNA(LOOKUP(A2,G$2:g$1474)),"","Match")
> >
> > But match is not being returned for any records
> > sample of data
> >
> > Isomedia MAC# Unit ServiceStart Match Div_Lot RegNumber MAC
> > 1023201471 050-0026 1/20/04 050-0026 1023201471 0002A11B2730
> > 1037653266 025-016R 5/25/04 025-016R 1037653266 0002A1188B60
> > 1045954143 0002A11BC470 007-0019 6/11/04 007-0019 1045954143 0002A11BC470
> > 1048849824 0002A119D1F0 051-0004 12/8/04 051-0004 1048849824 0002A119D1F0
> > 1055082119 025-091R 6/9/04 025-091R 1055082119 0002A1192DA0
> >
> >
> >
> > --
> > Oscar Kelley
> > HFN Data Analyst
> > Highlands Fiber Network
> >

[Max]

"Oscar Kelley - Salmon Days Festival" wrote:
> Thank it didn't work. ???? I even made sure both columns were formatted the
> same.

Formatting doesn't change the underlying values ..

> Column A has 1241 records, column G 1474 - 233 increase. I trying to match
> the ones that are the same.
> What happened when I changed it to ISNUMBER, all but 3 were FALSE, whereas
> it should be closer to 1241, except for those that are new or terminated.

Believe that the earlier formulas in col K are working ok.
Since you want:
> .. the ones that are the same
then the logic should be to autofilter col K for TRUE
(which would return the fig closer to 1241)

FALSE returns the items in col A which are *not found* in col G
Autofiltering FALSE however enables you to see/inspect these items

To complete the comparison, the converse should be done to compare col G's
items against col A's. Similarly ..
Put in L2: =ISNUMBER(MATCH(G2,A:A,0))
Copy L2 down to the last row of data in col G
Col L will return TRUE if the item in col G is found in col A, FALSE
otherwise.
Then just autofilter TRUE / FALSE on col L as desired for closer inspection
...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Oscar Kelley - Salmon Days Festival]

THANKS Max,

Got it to work once I defined an array range versus entire column

i.e. =ISNUMBER(MATCH(G2,A$2:A$1481,0))


--
Oscar Kelley
HFN Data Analyst
Highlands Fiber Network



"Max" wrote:

> "Oscar Kelley - Salmon Days Festival" wrote:
> > Thank it didn't work. ???? I even made sure both columns were formatted the
> > same.
>
> Formatting doesn't change the underlying values ..
>
> > Column A has 1241 records, column G 1474 - 233 increase. I trying to match
> > the ones that are the same.
> > What happened when I changed it to ISNUMBER, all but 3 were FALSE, whereas
> > it should be closer to 1241, except for those that are new or terminated.
>
> Believe that the earlier formulas in col K are working ok.
> Since you want:
> > .. the ones that are the same
> then the logic should be to autofilter col K for TRUE
> (which would return the fig closer to 1241)
>
> FALSE returns the items in col A which are *not found* in col G
> Autofiltering FALSE however enables you to see/inspect these items
>
> To complete the comparison, the converse should be done to compare col G's
> items against col A's. Similarly ..
> Put in L2: =ISNUMBER(MATCH(G2,A:A,0))
> Copy L2 down to the last row of data in col G
> Col L will return TRUE if the item in col G is found in col A, FALSE
> otherwise.
> Then just autofilter TRUE / FALSE on col L as desired for closer inspection
> ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

[Max]

Great to hear that, Oscar
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Oscar Kelley - Salmon Days Festival" wrote:
> THANKS Max,
> Got it to work once I defined an array range versus entire column
> i.e. =ISNUMBER(MATCH(G2,A$2:A$1481,0))