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 |
#1
|
|||
|
|||
VLOOKUP problem
Hi,
I am struggling to find a solution to match my requirements & would appreciate it if somebody can help me out. I have a spreadsheet with just under 4500 lines of information. For each line, there are around 10 entries (i.e.... 'description', 'shipment number', 'order number', 'cost per item', 'supplier', 'shipment method' etc). I have a second spreadsheet which contains a completely different set of information with the only common reference between the two spreadsheets being the 'order number'. I am looking to do a 'VLOOKUP' type search in my 2nd spreadsheet, to reference the 'order number' in the 1st spreadsheet & return the result from the shipment method column. My problem: Spreadsheet 1 contains all of the information I require, but the cells in the 'order number' column contain more than one reference all within a single cell (i.e.... "PO1234, PO1235, PO1237"). So, if I do a VLOOKUP and the lookup value of the cell I wish to lookup is "PO1235", it will not be found because there is no cell in my 2nd spreadsheet with just"PO1235" in it. It will be in a cell with "PO1234, PO1235, PO1237", in it. I need VLOOKUP to find my cell reference within a cell within and then return a value from a corresponding line (shipment method). Thanks, Jason |
#2
|
|||
|
|||
One way
=INDEX(Sheet3!$B$2:$B$4000,MATCH(TRUE,ISNUMBER(FIN D("PO1234",Sheet3!$G$2:$G$4000)),0)) entered with ctrl + shift & enter where Sheet3!G2:G4000 is the range that holds the shipment method and Sheet3!B2:B4000 the range that holds the shipment number Regards, Peo Sjoblom "Jason" wrote: Hi, I am struggling to find a solution to match my requirements & would appreciate it if somebody can help me out. I have a spreadsheet with just under 4500 lines of information. For each line, there are around 10 entries (i.e.... 'description', 'shipment number', 'order number', 'cost per item', 'supplier', 'shipment method' etc). I have a second spreadsheet which contains a completely different set of information with the only common reference between the two spreadsheets being the 'order number'. I am looking to do a 'VLOOKUP' type search in my 2nd spreadsheet, to reference the 'order number' in the 1st spreadsheet & return the result from the shipment method column. My problem: Spreadsheet 1 contains all of the information I require, but the cells in the 'order number' column contain more than one reference all within a single cell (i.e.... "PO1234, PO1235, PO1237"). So, if I do a VLOOKUP and the lookup value of the cell I wish to lookup is "PO1235", it will not be found because there is no cell in my 2nd spreadsheet with just"PO1235" in it. It will be in a cell with "PO1234, PO1235, PO1237", in it. I need VLOOKUP to find my cell reference within a cell within and then return a value from a corresponding line (shipment method). Thanks, Jason |
#3
|
|||
|
|||
Another option:
=VLOOKUP("*"&A1&"*",sheet2!a1:b999,2,FALSE) Jason wrote: Hi, I am struggling to find a solution to match my requirements & would appreciate it if somebody can help me out. I have a spreadsheet with just under 4500 lines of information. For each line, there are around 10 entries (i.e.... 'description', 'shipment number', 'order number', 'cost per item', 'supplier', 'shipment method' etc). I have a second spreadsheet which contains a completely different set of information with the only common reference between the two spreadsheets being the 'order number'. I am looking to do a 'VLOOKUP' type search in my 2nd spreadsheet, to reference the 'order number' in the 1st spreadsheet & return the result from the shipment method column. My problem: Spreadsheet 1 contains all of the information I require, but the cells in the 'order number' column contain more than one reference all within a single cell (i.e.... "PO1234, PO1235, PO1237"). So, if I do a VLOOKUP and the lookup value of the cell I wish to lookup is "PO1235", it will not be found because there is no cell in my 2nd spreadsheet with just"PO1235" in it. It will be in a cell with "PO1234, PO1235, PO1237", in it. I need VLOOKUP to find my cell reference within a cell within and then return a value from a corresponding line (shipment method). Thanks, Jason -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Vlookup Problem | pauluk | General Discussion | 3 | September 15th, 2004 07:48 PM |
Vlookup and IF function problem | yuste | General Discussion | 4 | June 24th, 2004 07:43 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
Vlookup Accuracy problem | Mervyn Thomas | Worksheet Functions | 4 | February 19th, 2004 10:05 AM |