Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookups & match ????
Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in
a colunm, match this numeric value exactly in another column and then return a value from a third column to a new column. For example: column 1, cell 1 'corr/joebloggs/445544/transfer' I want to find the '445544' sitting in column 2, then return a value (say 12345) sitting in column 3 to a new column, 4 Its confusing me just writing this!!! Please help!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookups & match ????
In cell A1:
corr/joebloggs/445544/transfer If you mean 445544 in any of the row in column B and a corresponding number in ColumnC in the same row In cell D1 =INDEX(C:C,MATCH(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))),B:B,0)) If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in a colunm, match this numeric value exactly in another column and then return a value from a third column to a new column. For example: column 1, cell 1 'corr/joebloggs/445544/transfer' I want to find the '445544' sitting in column 2, then return a value (say 12345) sitting in column 3 to a new column, 4 Its confusing me just writing this!!! Please help!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookups & match ????
Hi Jacob,
If I can expand my example, it may help me and you!! I have: Col A Col B Col C Col D 'corr/joebloggs/445544/transfer' 263636 8701111 MATCH COL A&B to give C 'corr/joebloggs/554455/transfer' 263637 8702222 MATCH COL A&B to give C 'corr/joebloggs/263636/transfer' 554455 8703333 MATCH COL A&B to give C 'corr/joebloggs/263637/transfer' 445544 8704444 MATCH COL A&B to give C So for the first row, the result in D1 should match A1 with B4 to give number found in C4. I do hope you can help.....Thanks very much, Andy "Jacob Skaria" wrote: In cell A1: corr/joebloggs/445544/transfer If you mean 445544 in any of the row in column B and a corresponding number in ColumnC in the same row In cell D1 =INDEX(C:C,MATCH(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))),B:B,0)) If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in a colunm, match this numeric value exactly in another column and then return a value from a third column to a new column. For example: column 1, cell 1 'corr/joebloggs/445544/transfer' I want to find the '445544' sitting in column 2, then return a value (say 12345) sitting in column 3 to a new column, 4 Its confusing me just writing this!!! Please help!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookups & match ????
Assuming the desired number will always be followed by "/", try this array
formula (commit with CTRL+SHIFT+ENTER): =VLOOKUP(--LEFT(SUBSTITUTE(MID(A1,MATCH(1, --ISNUMBER(--MID(A1,ROW($1:$1000),1)),0),LEN(A1)), "/",REPT(" ",99)),99),$B$1:$C$1000,2,FALSE) Adjust the range near the end ($B$1:$C$1000) to match your data. starfishgooner wrote: Hi Jacob, If I can expand my example, it may help me and you!! I have: Col A Col B Col C Col D 'corr/joebloggs/445544/transfer' 263636 8701111 MATCH COL A&B to give C 'corr/joebloggs/554455/transfer' 263637 8702222 MATCH COL A&B to give C 'corr/joebloggs/263636/transfer' 554455 8703333 MATCH COL A&B to give C 'corr/joebloggs/263637/transfer' 445544 8704444 MATCH COL A&B to give C So for the first row, the result in D1 should match A1 with B4 to give number found in C4. I do hope you can help.....Thanks very much, Andy "Jacob Skaria" wrote: In cell A1: corr/joebloggs/445544/transfer If you mean 445544 in any of the row in column B and a corresponding number in ColumnC in the same row In cell D1 =INDEX(C:C,MATCH(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))),B:B,0)) If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in a colunm, match this numeric value exactly in another column and then return a value from a third column to a new column. For example: column 1, cell 1 'corr/joebloggs/445544/transfer' I want to find the '445544' sitting in column 2, then return a value (say 12345) sitting in column 3 to a new column, 4 Its confusing me just writing this!!! Please help!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookups & match ????
Did you try the formula?
If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Jacob, If I can expand my example, it may help me and you!! I have: Col A Col B Col C Col D 'corr/joebloggs/445544/transfer' 263636 8701111 MATCH COL A&B to give C 'corr/joebloggs/554455/transfer' 263637 8702222 MATCH COL A&B to give C 'corr/joebloggs/263636/transfer' 554455 8703333 MATCH COL A&B to give C 'corr/joebloggs/263637/transfer' 445544 8704444 MATCH COL A&B to give C So for the first row, the result in D1 should match A1 with B4 to give number found in C4. I do hope you can help.....Thanks very much, Andy "Jacob Skaria" wrote: In cell A1: corr/joebloggs/445544/transfer If you mean 445544 in any of the row in column B and a corresponding number in ColumnC in the same row In cell D1 =INDEX(C:C,MATCH(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))),B:B,0)) If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in a colunm, match this numeric value exactly in another column and then return a value from a third column to a new column. For example: column 1, cell 1 'corr/joebloggs/445544/transfer' I want to find the '445544' sitting in column 2, then return a value (say 12345) sitting in column 3 to a new column, 4 Its confusing me just writing this!!! Please help!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookups & match ????
In my newsreader, when I copy the formula from your original message and paste
in Excel, I get "#N/A". When I looked closer, it was because I ended up with "9999" instead of what you intended. When quoted below, the formula shows "99 ^ 99" (without the spaces) and copies OK. But the original message showed the second 99 in superscript without the "^" and caused the error. Could be why it didn't work for the OP... Jacob Skaria wrote: Did you try the formula? If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Jacob, If I can expand my example, it may help me and you!! I have: Col A Col B Col C Col D 'corr/joebloggs/445544/transfer' 263636 8701111 MATCH COL A&B to give C 'corr/joebloggs/554455/transfer' 263637 8702222 MATCH COL A&B to give C 'corr/joebloggs/263636/transfer' 554455 8703333 MATCH COL A&B to give C 'corr/joebloggs/263637/transfer' 445544 8704444 MATCH COL A&B to give C So for the first row, the result in D1 should match A1 with B4 to give number found in C4. I do hope you can help.....Thanks very much, Andy "Jacob Skaria" wrote: In cell A1: corr/joebloggs/445544/transfer If you mean 445544 in any of the row in column B and a corresponding number in ColumnC in the same row In cell D1 =INDEX(C:C,MATCH(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))),B:B,0)) If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in a colunm, match this numeric value exactly in another column and then return a value from a third column to a new column. For example: column 1, cell 1 'corr/joebloggs/445544/transfer' I want to find the '445544' sitting in column 2, then return a value (say 12345) sitting in column 3 to a new column, 4 Its confusing me just writing this!!! Please help!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookups & match ????
Try this:
=VLOOKUP(TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),198,99))+0,$B$1:$C$4,2,0) "starfishgooner" wrote: Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in a colunm, match this numeric value exactly in another column and then return a value from a third column to a new column. For example: column 1, cell 1 'corr/joebloggs/445544/transfer' I want to find the '445544' sitting in column 2, then return a value (say 12345) sitting in column 3 to a new column, 4 Its confusing me just writing this!!! Please help!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookups & match ????
Thanks Glenn..I was wondering what went wrong !!
If this post helps click Yes --------------- Jacob Skaria "Glenn" wrote: In my newsreader, when I copy the formula from your original message and paste in Excel, I get "#N/A". When I looked closer, it was because I ended up with "9999" instead of what you intended. When quoted below, the formula shows "99 ^ 99" (without the spaces) and copies OK. But the original message showed the second 99 in superscript without the "^" and caused the error. Could be why it didn't work for the OP... Jacob Skaria wrote: Did you try the formula? If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Jacob, If I can expand my example, it may help me and you!! I have: Col A Col B Col C Col D 'corr/joebloggs/445544/transfer' 263636 8701111 MATCH COL A&B to give C 'corr/joebloggs/554455/transfer' 263637 8702222 MATCH COL A&B to give C 'corr/joebloggs/263636/transfer' 554455 8703333 MATCH COL A&B to give C 'corr/joebloggs/263637/transfer' 445544 8704444 MATCH COL A&B to give C So for the first row, the result in D1 should match A1 with B4 to give number found in C4. I do hope you can help.....Thanks very much, Andy "Jacob Skaria" wrote: In cell A1: corr/joebloggs/445544/transfer If you mean 445544 in any of the row in column B and a corresponding number in ColumnC in the same row In cell D1 =INDEX(C:C,MATCH(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))),B:B,0)) If this post helps click Yes --------------- Jacob Skaria "starfishgooner" wrote: Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in a colunm, match this numeric value exactly in another column and then return a value from a third column to a new column. For example: column 1, cell 1 'corr/joebloggs/445544/transfer' I want to find the '445544' sitting in column 2, then return a value (say 12345) sitting in column 3 to a new column, 4 Its confusing me just writing this!!! Please help!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formating, match, lookups | Excel Worksheet Functions | |||
How to use index match for multi lookups? | Excel Worksheet Functions | |||
INDEX / MATCH performance for lookups | Excel Worksheet Functions | |||
lookups and match | Excel Worksheet Functions | |||
Lookups vs Match | Excel Worksheet Functions |