Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up a value in one column to another colum
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up a value in one column to another colum
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up a value in one column to another colum
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 -- exxon99 ------------------------------------------------------------------------ exxon99's Profile: http://www.excelforum.com/member.php...o&userid=34962 View this thread: http://www.excelforum.com/showthread...hreadid=551241 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up a value in one column to another colum
"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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up a value in one column to another colum
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up a value in one column to another colum
"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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up a value in one column to another colum
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up a value in one column to another colum
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I combine column A into Colum B? | Excel Discussion (Misc queries) | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
match and count words | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) |