Trying to match information

[Chris T]

Hi! I am needing help trying to find a formula that will help me match the popular description in one spreadsheet to the universal description in another spreadsheet using either the Item ID or Mfg Item ID. My goal is to be able to match as many popular descriptions to the universal descriptions then copy and paste the popular decription in the column of the universal description. Keeping in mind that the spreadsheets are not going to have all the same information- one spreadsheet might have 100 lines and the other may have 200 lines (for example: below, spreadsheet 2 has Item ID 000457 but spreadsheet one does not have that item). Also, I don't know if this matters or not, but the cells that contain both the Item ID and Mfg Item ID are all formated as text b/c I have to keep the zeros in front.


Spreadsheet One looks like this

[pre]

Item ID	Mfg Itm ID	Popular Descript
000117	258062WCALL	COTTON TIP APPLICATOR
000128	6070        	TOOTHETTES
000228	8884417601	GAUZE VASELINE- 1X8
000295	4914.ORG	BIOHAZARD BAGS
000388	6114	        DRSNG NON-ADHERING- 3X16
000460	7541	        TELFA- 4X8
Spreadsheet Two looks like this

Item	Mfg Itm ID	Universal Descript
000117	258062WCALL	APL FBRTP 6IN REG STRL LATEX FREE CTTN CTNTP WOOD
000295	4914.ORG	BAG 9X6IN 2 PCKT ZPLK BHZR LOGO 2 MIL 32OZ SPECIMEN
000388	6114		CURITY 16X3IN NADH KNTD WOUND OIL EMUL STRL
000457	R1548		STRIP 5X1IN STRSTRP ADHERENT HYPOALLERGENIC POR
000460	7541		DSG TELFA 8X4IN ABSRB NWVN ILND ADHESIVE CTTN STRL

[/pre]
Any help would be greatly appreciated!

 

[Luke M]

This is designed for XL 2007+

Checks Item # and then the Mfg Itm ID to see if a match can be found.

=IFERROR(VLOOKUP(A2,'Sheet 1'!A:C,3,FALSE),IFERROR(VLOOKUP(B2,'Sheet 1'!B:C,2,FALSE),"Not found"))

 

[Chris T]

Hi Luke,


I tried the formula you gave me....first off thank you....I got farther than I have in a week trying to figure this out. However, all matches came back as not found (and I know there are several items that are in both areas (spreadsheets). I tried the formula in both sheets and got the same results. I'm hoping you can help me figure out what I did wrong. Thanks!

 

[NARAYANK991]

Hi Chris ,


Please note that Luke has used the generic 'Sheet 1' , which you might not have in your workbook ; this looks like Sheet1 , but is different !


Replace 'Sheet 1' by the relevant name of your tab , and everything should work correctly.


Narayan

 

[Chris T]

Thanks Narayan!

Before I try it again, I do have a quick question.

Since my information is in two different worksheets, do I need to insert another worksheet in one of them so that way my information is all in one workbook or should it work they I have them now?


Thanks!

Chris

 

[NARAYANK991]

Hi Chris ,


It should work with two separate workbooks ; however , if there is no specific reason to have two separate workbooks , it is preferable to have all your data together ; your formulae are shorter , macro programming is easier since you don't need to open and close workbooks , transferring data by email is easier , there are several benefits to keeping all your data together. Some functions like INDIRECT won't work with closed external links.


The plus to having them in separate workbooks is if you wish to enforce permissions and have privacy.


Narayan