| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP with value contained within text
Can someone please help.
I am trying to perform a vlookup where the value is contained within the text of the cell and there is no common pattern as to where the value starts (eg could start in position 1 or 25) An example: The value is contained within the following cell in "Sheet 1" - A/C M98603521 GOLDMAN SACH Within the array in "Sheet 2" Column A Column B GOLDMAN SACH GOLSAC1012 .... .... I would like to lookup A/C M98603521 GOLDMAN SACH and return GOLSAC1012. Thanks Dan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP with value contained within text
Try one of these:
=LOOKUP(2,1/((ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)))*(Sheet2!A$ 1:A$10<"")),Sheet2!B$1:B$10) =INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX((ISNUMBER(SEA RCH(Sheet2!A$1:A$10,A1)))*(Sheet2!A$1:A$10<""),,1 ),0)) If your table on sheet2 will not have any empty cells we can shorten each formula a little: =LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)),Sheet2!B$1:B$ 10) =INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX(--(ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1))),,1),0)) Biff "Farns" wrote in message ... Can someone please help. I am trying to perform a vlookup where the value is contained within the text of the cell and there is no common pattern as to where the value starts (eg could start in position 1 or 25) An example: The value is contained within the following cell in "Sheet 1" - A/C M98603521 GOLDMAN SACH Within the array in "Sheet 2" Column A Column B GOLDMAN SACH GOLSAC1012 ... ... I would like to lookup A/C M98603521 GOLDMAN SACH and return GOLSAC1012. Thanks Dan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP with value contained within text
The formula for the last match in the list can be shortened a little:
=LOOKUP(2,1/SEARCH(Sheet2!A$1:A$10,A2)/(Sheet2!A$1:A$10<""),Sheet2!B $1:B$10) or without blanks: =LOOKUP(9^9,SEARCH(Sheet2!A$1:A$10,A2),Sheet2!B$1: B$10) You could also add in the first line of sheet1 the return value when the match is not found: A B * Not found A/C M98603521 GOLDMAN SACH ..... On 12 Mar, 02:30, "T. Valko" wrote: Try one of these: =LOOKUP(2,1/((ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)))*(Sheet2!A$ 1:A$10<"")),*Sheet2!B$1:B$10) =INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX((ISNUMBER(SEA RCH(Sheet2!A$1:A$10,A1)))**(Sheet2!A$1:A$10<""),, 1),0)) If your table on sheet2 will not have any empty cells we can shorten each formula a little: =LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)),Sheet2!B$1:B$ 10) =INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX(--(ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)*)),,1),0)) Biff "Farns" wrote in message ... Can someone please help. I am trying to perform a vlookup where the value is contained within the text of the cell and there is no common pattern as to where the value starts (eg could start in position 1 or 25) An example: The value is contained within the following cell in "Sheet 1" - A/C M98603521 GOLDMAN SACH Within the array in "Sheet 2" Column A Column B GOLDMAN SACH GOLSAC1012 ... ... I would like to lookup A/C M98603521 GOLDMAN SACH and return GOLSAC1012. Thanks Dan- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP with value contained within text
Thank you both very much.
Dan "Lori" wrote: The formula for the last match in the list can be shortened a little: =LOOKUP(2,1/SEARCH(Sheet2!A$1:A$10,A2)/(Sheet2!A$1:A$10<""),Sheet2!B $1:B$10) or without blanks: =LOOKUP(9^9,SEARCH(Sheet2!A$1:A$10,A2),Sheet2!B$1: B$10) You could also add in the first line of sheet1 the return value when the match is not found: A B * Not found A/C M98603521 GOLDMAN SACH ..... On 12 Mar, 02:30, "T. Valko" wrote: Try one of these: =LOOKUP(2,1/((ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)))*(Sheet2!A$ 1:A$10<"")),-Sheet2!B$1:B$10) =INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX((ISNUMBER(SEA RCH(Sheet2!A$1:A$10,A1)))-*(Sheet2!A$1:A$10<""),,1),0)) If your table on sheet2 will not have any empty cells we can shorten each formula a little: =LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)),Sheet2!B$1:B$ 10) =INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX(--(ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)-)),,1),0)) Biff "Farns" wrote in message ... Can someone please help. I am trying to perform a vlookup where the value is contained within the text of the cell and there is no common pattern as to where the value starts (eg could start in position 1 or 25) An example: The value is contained within the following cell in "Sheet 1" - A/C M98603521 GOLDMAN SACH Within the array in "Sheet 2" Column A Column B GOLDMAN SACH GOLSAC1012 ... ... I would like to lookup A/C M98603521 GOLDMAN SACH and return GOLSAC1012. Thanks Dan- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find partial text contained in another cell | Excel Discussion (Misc queries) | |||
| When I import a csv file text that contained a + gets scrambled | Excel Discussion (Misc queries) | |||
| Countif function for instances of text string contained | Excel Worksheet Functions | |||
| Vlookup for data contained in a cell | Excel Worksheet Functions | |||
| searching a cell for a contained text word | Charts and Charting in Excel | |||
Linear Mode
