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! |
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! |
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! |
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! |
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! |
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! |
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! |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com