Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup to find a match in a string
Hello, I am trying to use vlookup to match the contents of a cell with a
portion of a text string and return another cells data. Maybe using Index and Match would be better but I can't figure out how to accomplish this wtih either of them. For example: Sheet1 Column A contains a list of Sales Oder numbers, one Sales Order per cell. Sheet2 Column C contains several Sales Order numbers in each cell. Using the Sales Order number in Sheet1 column A I need to find that Sales Order number in Sheet2 Column C and return Column D to Sheet 1. Any help is greatly appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup to find a match in a string
Hi,
In sheet1 column B enter =Vlookup(A1,sheet2!$C:$D,2,false), if not using excel 2007 use =Vlookup(A1,sheet2!$C1:$D1000,2,false), "KCK" wrote: Hello, I am trying to use vlookup to match the contents of a cell with a portion of a text string and return another cells data. Maybe using Index and Match would be better but I can't figure out how to accomplish this wtih either of them. For example: Sheet1 Column A contains a list of Sales Oder numbers, one Sales Order per cell. Sheet2 Column C contains several Sales Order numbers in each cell. Using the Sales Order number in Sheet1 column A I need to find that Sales Order number in Sheet2 Column C and return Column D to Sheet 1. Any help is greatly appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup to find a match in a string
Hi,
Here is one array solution, you will need to add the sheet references: =INDEX(D1:D10,MAX(ROW(C1:C10)*ISNUMBER(SEARCH(A1,C 1:C10))),0) To make it an array press Shift+Ctrl+Enter to enter it. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "KCK" wrote: Hello, I am trying to use vlookup to match the contents of a cell with a portion of a text string and return another cells data. Maybe using Index and Match would be better but I can't figure out how to accomplish this wtih either of them. For example: Sheet1 Column A contains a list of Sales Oder numbers, one Sales Order per cell. Sheet2 Column C contains several Sales Order numbers in each cell. Using the Sales Order number in Sheet1 column A I need to find that Sales Order number in Sheet2 Column C and return Column D to Sheet 1. Any help is greatly appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup to find a match in a string
Try this (change 100 to your last row on Sheet1)
=VLOOKUP(LOOKUP(2,1/IF(FIND(A1,Sheet2!$C$1:$C$100)0,ROW(),0),Sheet2!$ C$1:$C$100),Sheet2!C:D,2,FALSE) You will have to use CTRL-SHIFT-ENTER instead on normal ENTER after typing/pasting the formula in B1... You can then copy the formula down... Note that you will get the LAST match if there are more than one rows with matching Sales Order in Sheet2!C... and #N/A if there is no match. Let us know how it goes. "KCK" wrote: Hello, I am trying to use vlookup to match the contents of a cell with a portion of a text string and return another cells data. Maybe using Index and Match would be better but I can't figure out how to accomplish this wtih either of them. For example: Sheet1 Column A contains a list of Sales Oder numbers, one Sales Order per cell. Sheet2 Column C contains several Sales Order numbers in each cell. Using the Sales Order number in Sheet1 column A I need to find that Sales Order number in Sheet2 Column C and return Column D to Sheet 1. Any help is greatly appreciated! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup to find a match in a string
It would have been a good idea to show us some samples.
Here's a generic array formula** that should get you headed in the right direction: =INDEX(Sheet2!D1:D5,MATCH(TRUE,ISNUMBER(SEARCH(A1, Sheet2!C1:C5)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "KCK" wrote in message ... Hello, I am trying to use vlookup to match the contents of a cell with a portion of a text string and return another cells data. Maybe using Index and Match would be better but I can't figure out how to accomplish this wtih either of them. For example: Sheet1 Column A contains a list of Sales Oder numbers, one Sales Order per cell. Sheet2 Column C contains several Sales Order numbers in each cell. Using the Sales Order number in Sheet1 column A I need to find that Sales Order number in Sheet2 Column C and return Column D to Sheet 1. Any help is greatly appreciated! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup to find a match in a string
Eduardo,
KCK want to do the lookup when A1 is part of the string in a cell in Sheet2!C... Also Excel 2003 works with =Vlookup(A1,sheet2!C:D,2,false) Absolute reference is not required since you are using the whole column... "Eduardo" wrote: Hi, In sheet1 column B enter =Vlookup(A1,sheet2!$C:$D,2,false), if not using excel 2007 use =Vlookup(A1,sheet2!$C1:$D1000,2,false), "KCK" wrote: Hello, I am trying to use vlookup to match the contents of a cell with a portion of a text string and return another cells data. Maybe using Index and Match would be better but I can't figure out how to accomplish this wtih either of them. For example: Sheet1 Column A contains a list of Sales Oder numbers, one Sales Order per cell. Sheet2 Column C contains several Sales Order numbers in each cell. Using the Sales Order number in Sheet1 column A I need to find that Sales Order number in Sheet2 Column C and return Column D to Sheet 1. Any help is greatly appreciated! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup to find a match in a string
Thanks to everyone, your input was great. I got Shane Devonshires to work
perfectly! I really appreciate all your help! "T. Valko" wrote: It would have been a good idea to show us some samples. Here's a generic array formula** that should get you headed in the right direction: =INDEX(Sheet2!D1:D5,MATCH(TRUE,ISNUMBER(SEARCH(A1, Sheet2!C1:C5)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "KCK" wrote in message ... Hello, I am trying to use vlookup to match the contents of a cell with a portion of a text string and return another cells data. Maybe using Index and Match would be better but I can't figure out how to accomplish this wtih either of them. For example: Sheet1 Column A contains a list of Sales Oder numbers, one Sales Order per cell. Sheet2 Column C contains several Sales Order numbers in each cell. Using the Sales Order number in Sheet1 column A I need to find that Sales Order number in Sheet2 Column C and return Column D to Sheet 1. Any help is greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup and finding text string that's not an exact match | Excel Discussion (Misc queries) | |||
How do I use VLOOKUP to find part of string? | Excel Worksheet Functions | |||
Partial String Match & Wild Cards Using VLOOKUP | Excel Worksheet Functions | |||
Partial String Match Using VLOOKUP | Excel Worksheet Functions | |||
Find a not exact match using vlookup | Excel Discussion (Misc queries) |