If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
VLookup
Put this into say, F2, normal ENTER will do:
=IF(SUMPRODUCT((ISNUMBER(MATCH(C2,D$2$600,0)))*( ISNUMBER(MATCH(C2,E$2:E$600,0))))0,C2,"") Copy down to the last row of data in col C. Col F will return the desired results. Adapt the ranges to suit the actual extents. voila? wave it, hit YES below -- Max Singapore --- "L.Mathe" wrote: Hi Max, A sample of the WS I have: Col. C Col. D Col. E 4-Mar-10 5-Mar-10 6-Mar-10 6333880064104573811 6333880064111021143 6333880064143241511 6333880064109655852 6333880064132072661 6333880064125485573 6333880064143241511 6333880064132072661 6333880064147798581 6333880064147798581 6333880064143241511 6333880064206960742 There could be as many as 500 rows of data per in column, but will vary in the number of rows. Using the numbers in Col. C, I need to see if it exists in BOTH Col. D & E. If the number is in all 3 columns, I need it either hightlighted, displayed in another column, or something to reference the number. In my original submission to this forum, as stated, what I am doing right now is, in Col. A I have VLookup formula (=VLOOKUP(Sheet1!C3,Sheet1!D$2$1000,1,FALSE) that gives me the numbers that match in Col. C & D. In Col. B, VLookup (=VLOOKUP(Sheet1!A3,Sheet1!E$2:E$1000,1,FALSE) gives me a match of Col. A & D. So the end results would look like: Col. A Col. B #N/A #N/A #N/A #N/A 6333880064143241511 6333880064143241511 #N/A #N/A I hope this helps. But, please don't feel a solution must be found, as what I have works right now. I thought there might be a better (easier) way of accomplishing the same. I am relatively new to Excel (about 2 years into it), and always looking to improve what I have. Thanks! -- Linda |
#12
|
|||
|
|||
VLookup
Former was overkill, sorry. This suffices in F2, copied down,
but I think Bob has beaten me to it: =IF(AND(ISNUMBER(MATCH(C2,D,0)),ISNUMBER(MATCH(C 2,E:E,0))),C2,"") -- Max Singapore --- |
#13
|
|||
|
|||
VLookup
You guys are GREAT! - This works.
Thank you!! -- Linda "Max" wrote: Former was overkill, sorry. This suffices in F2, copied down, but I think Bob has beaten me to it: =IF(AND(ISNUMBER(MATCH(C2,D,0)),ISNUMBER(MATCH(C 2,E:E,0))),C2,"") -- Max Singapore --- |
#14
|
|||
|
|||
VLookup
You guys are GREAT! - This works.
Thank you!! -- Linda "Bob Phillips" wrote: Try =IF(AND(ISNUMBER(MATCH(C3,D,0)),ISNUMBER(MATCH(C 3,E:E,0))),C3,"") -- HTH Bob "L.Mathe" wrote in message ... Hi Max, A sample of the WS I have: Col. C Col. D Col. E 4-Mar-10 5-Mar-10 6-Mar-10 6333880064104573811 6333880064111021143 6333880064143241511 6333880064109655852 6333880064132072661 6333880064125485573 6333880064143241511 6333880064132072661 6333880064147798581 6333880064147798581 6333880064143241511 6333880064206960742 There could be as many as 500 rows of data per in column, but will vary in the number of rows. Using the numbers in Col. C, I need to see if it exists in BOTH Col. D & E. If the number is in all 3 columns, I need it either hightlighted, displayed in another column, or something to reference the number. In my original submission to this forum, as stated, what I am doing right now is, in Col. A I have VLookup formula (=VLOOKUP(Sheet1!C3,Sheet1!D$2$1000,1,FALSE) that gives me the numbers that match in Col. C & D. In Col. B, VLookup (=VLOOKUP(Sheet1!A3,Sheet1!E$2:E$1000,1,FALSE) gives me a match of Col. A & D. So the end results would look like: Col. A Col. B #N/A #N/A #N/A #N/A 6333880064143241511 6333880064143241511 #N/A #N/A I hope this helps. But, please don't feel a solution must be found, as what I have works right now. I thought there might be a better (easier) way of accomplishing the same. I am relatively new to Excel (about 2 years into it), and always looking to improve what I have. Thanks! -- Linda -- Linda "Max" wrote: That was a guess. Think its best that you illustrate your query/scenario. Paste some sample data before/after, show what you have, explain what you want to make happen. -- Max Singapore --- "L.Mathe" wrote: Sorry, this didn't work. What I have to accomplish is to find if what is in Col. C is in D & E, so that I have a match in all 3 columns. The data could be in any row from 2 to approximatley 500. I think it would need some kind of array formula which I am not familiar with. Thanks for your input. I find this forum is has so many helpful people and so many great suggestions! . |
#15
|
|||
|
|||
VLookup
Welcome, Linda
-- Max Singapore "L.Mathe" wrote in message ... You guys are GREAT! - This works. Thank you!! -- Linda |
|
Thread Tools | |
Display Modes | |
|
|