# Look Ups

#### DGB

##### Board Regular
I have a spreadsheet with 3 columns. The first column has a list of numbers, the second column has a list of numbers, and the third column has a code, one of which is “IP”

I want to look up IP, when I find it I need to get the number in the column to it’s left, on the same row. Then I need to look up that number in the first column (which will be on a separate row). I then need to change the word in the third column of that row to “Kit”.

Example:

Col. A has numbers
Col. B has numbers, some of which are the same as in Col. A, but not in the same row
Col. C has a code, one of which is ‘IP’

I want to look for ‘IP’ in column C. When I find it, I want to get the number corresponding number from Col. B (same row), then look up that number in Col. A. (remember, Col. A and Col. B have some of the same numbers, they are just not in the same row).

When I find that number in Col. A, I want to change the code that is in Col. C, from that same row, to read “Kit”

Example: I find “IP” in Col. C. The coinciding number in Col. B is “4630.0”. I find that number in Col. A (one row down) and need to change the “CP” in Col. C to “Kit”.

Col. A Col. B Col. C
12345.0 46730.0 IP
46730.0 CP – (this needs to be changed to “Kit”)

Hopefully this makes sence. The list I have is 10,000 plus rows long, and the rows where the part numbers match are usually not close to each other. One is closer to the bottom of the list, while the other is closer to the top of the list.

Thanks

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Special-K99

##### Well-known Member
How do you go about "finding" IP? Are you entering one code at a time?
Are there any duplicate values in column A or B?
Are either column A or B sorted?

#### DGB

##### Board Regular
Hi Special KK

Will you ever be searching for something other than “IP”? Answer: No
How do you go about "finding" IP? Answer: I was using the Search box, but would like to use a formula
Are there any duplicate values in column A or B? Answer: No
Are either column A or B sorted? Answer: Col B is sorted ASC

#### ShaunD30

##### Board Regular
I'll give this one a try..keep in mind I am a VBA noob, and my code is certainly not as elegant as others..but I think this may work...

Code:
``````Sub FindIP()
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("C1").Select
Do Until ActiveCell.Row = Finalrow
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "IP" Then
ActiveCell.Offset(0, -1).Select

CurrentNum = ActiveCell.Value

Range("A1").Select

For i = 1 To Finalrow
If Cells(i, 1).Value = CurrentNum Then
Cells(i, 3).Value = "Kit"

End If
Next i

End If

Loop
Range("A1").Select
End Sub``````

Hope this helps. Just save your work before you try

Shaun

#### DGB

##### Board Regular
Hi Shaun,

I created a test sheet witht e following bits of data:

ColA ColB ColC
12345 46730 IP
46730 12345 CP

And pasted your code into a macro. When i run it, nothing other than the cursor moving to Cell A1 happened.

Did I do something whong?

#### ShaunD30

##### Board Regular
The code i wrote assumed that there were column headers. Create column headers for those 3 columns and then try.

Shaun

#### DGB

##### Board Regular
that did it thank you.

How would the code change if there were no headers?

#### ShaunD30

##### Board Regular
DGB,

Honestly I am not sure. I tried to get it to work with no headers but got frustrated because my code would always skip over row 1. I know it was skipping because of the offset function but I tried to put the offset function in other places within the code and nothing seemed to work the way you needed it to..so I just created the code based on the assumption that there were headers.

I am sure someone else can tweak my code to have it look at row 1 also.

Shaun

#### DGB

##### Board Regular
well, given that my columns have headers, thee are no worries.

Thanks again for taking the time and sharing your knowledge.

#### ShaunD30

##### Board Regular
DGB- I tried a few more times and this code works if there are no headers

Code:
``````Sub FindIP()
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("C1").Select
Do Until ActiveCell.Row = Finalrow

If ActiveCell.Value = "IP" Then
ActiveCell.Offset(0, -1).Select

CurrentNum = ActiveCell.Value

Range("A1").Select

For i = 1 To Finalrow
If Cells(i, 1).Value = CurrentNum Then
Cells(i, 3).Value = "Kit"

End If
Next i

ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If

Loop
Range("A1").Select
End Sub``````

Again..not the best looking code in the world..or the most efficient..but it works

This is usually about the time an MVP speaks up and says "Why don't you just use this simple, easy to use one line formula =(insert basic formula)." haha

:D

Replies
3
Views
323
Replies
10
Views
218
Replies
3
Views
351
Replies
3
Views
302
Replies
8
Views
270

1,191,120
Messages
5,984,760
Members
439,909
Latest member
daigoku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back