If statement to match two sets of data

[ohwxguy]

I'm having some difficulty figuring out how to match two sets of data with a common field. I assume I'll need to do an if statement, though if it can be done another way, please let me know.

For example,

Data Set #1
Asset ID
#335566
#335568
#335569


Data Set #2
Asset ID, Cost
#335566, $35,000
#335567, $37,000
#335568, $38,000


I'd like Excel to take the asset ID in Data Set #1 and look through Data Set #2 for the same asset ID. If it finds a matching asset ID, I'd like Excel to pull the corresponding Cost value from data set #2 and place it into Data Set #1 (aligned with the correct asset). If it doesn't find the same asset ID in data set #2, it can leave the cell blank.

So essentially Data Set #1 becomes

Asset ID, Cost
#335566, $35,000
#335568, $38,000
#335569, [blank]


Any help would be greatly appreciated.

 

[id107]

Look into using Index and Match.

=IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0),0))

The above checks if A2 exists in column A on sheet2. If it does, it pulls the value from col B. If it doesn't exist, it leaves the cell "blank".

 

[328mike]

Hello,

Try using this to break apart the cost values of data set 2 into it's own column.

Dim DataRange As Long 'Counts the number of cells that contain data
    Dim i As Long
    DataRange = Application.WorksheetFunction.CountA(Range("A:A"))
    For i = 1 To DataRange
    Cells(1 + i, [insert your column number here]).Value = "=LEFT(RC[-1],FIND("","",RC[-1])-1)"
    Cells(1 + i, [Insert your column number here]).Value = "=TRIM(RIGHT(RC[-2],LEN(RC[-2])-LEN(RC[-1])))"
    Next i

 

[VoG]

Try VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[id107]

Hello,

Try using this to break apart the cost values of data set 2 into it's own column.

I had assumed that the commas were columns. Now I'm not so sure and feel a tad like a fool

 

[ohwxguy]

I had assumed that the commas were columns. Now I'm not so sure and feel a tad like a fool

The commas do represent separate columns.

 

[328mike]

I had assumed that the commas were columns. Now I'm not so sure and feel a tad like a fool

Heh, probably but I figured if it was one he could break it apart with what I wrote.

Edit: If they are separate columns it is pretty easy. Just use vlookup as suggested above.

=vlookup(A:A,Sheet 2!A:B,2,FALSE)

Put that formula next to your column A on Sheet 1 and you have your answer.

 

[328mike]

Edit 2: Note that when using vlookup if the asset id from data set 1 isn't in data set 2 you will get #N/A as the answer. You can remove this by copying your results column and doing paste special: values then do find and replace "#N/A" with "". I have tried coding an if statement to display nothing if vlookup results in N/A but haven't had any success.

 

[ohwxguy]

Thanks for the advice. It definitely helped!