Matching

[cj21]

I have 2 seperate data sets. The first gives a list of product codes an
a code. The second just gives the product code. e.g:

Dataset 1
Code Product code
01 02034953
01 02394685
01 04056606

02 05045677
02 05233456
02 90455439

03 04563956

Dataset 2

Product code
02034953
02394685
04056606
05045677
05233456
90455439
04563956

I want a way to get the code from dataset 1 to dataset 2(obviousl
matching products). Copying and pasting won't work because some of th
product codes in dataset 1 are not included in dataset 2.

Thankyou for your help

Chri

 

[Dave Peterson]

In sheet2:
in B2 (headers in row 1)

=index(sheet1!a:a,match(a2,sheet1!b:b,0))

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

 

[SteveG]

Chris,

=IF(ISNA(INDEX($A$2:$A$10,MATCH($B19,$B$2:$B$10,0),1)),"",INDEX($A$2:$A$10,MATCH($B19,$B$2:$B$10,0),1))

Where your Data Set 1 is A2:B10. B19:B26 is Data set 2 and A19:A26 i
where I put the formula to return the Code from Data Set 1.


HTH

Stev

 

[cj21]

not to sure how you've got this to work. Could you post an example.

Thanks for the help

Chri

 

[SteveG]

Chris,

Here is an example.




HTH

Steve


+-------------------------------------------------------------------+
|Filename: Index Match.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4280 |
+-------------------------------------------------------------------+