Add machine type next to machine number

**Trishay321** · 09-07-2011, 12:58 PM

I would like to make a nested If statement macro. I have 28 machine names R1-R28, I want to automatically insert the machine type next to the name. I.e. if R1,R5,R8 then 'Hartford 10' would need to be inserted in the cell next to the name. if R10,R22,R23 then 'Hartford 20' and so on.

I can alter the code just need to figure out the basic structure to insert the string into the adjacent cell.

**Leith Ross** · 09-07-2011, 01:40 PM

Hello Trishay321,

Without seeing the layout of the workbook I can only offer a guess as to what you need. This appears to be simple lookup problem. What you have tried so far? Can you post a copy of the workbook?

**Mordred** · 09-07-2011, 01:44 PM

I don't know without seeing your layout whether or not the following will work for you or even if it meets your criteria but try something like

Please Login or Register  to view this content.

You may need to upload a mock workbook with mock data (nothing personal or sensitive) in order to do exactly what you want.

EDIT: You beat me to it Leith

**Trishay321** · 09-07-2011, 02:12 PM

The first column is the machine name, I want to run the macro to fill in the type in the column next to it, so I was working on a bunch of if statements but there are 20 machine types.

R1 Hartford 10
R12 GW Menn 120
R10 Hartford 20
R11 Hartford 40

I had =IF(OR(A2="R1",A2="R5",A2="R8"),"Hartford 10", " ") to start but I will need something for each machine name and not sure how to put them all together in a macro.

I want the second column to populate itself with the macro.

**Leith Ross** · 09-07-2011, 04:00 PM

Hello Trishay321,

This workbook make help make this clearer. There are 2 worksheets: Machines and Lists. Lists contains 2 named ranged ranges "Machine_Number" and "Machine_Type". A formula on "Machines" looks up the machine number in column "A" and returns the machine type in column "B". Here is the formula that is used...

=IF(INDEX(Machine_Type,MATCH(A2,Machine_Number,0))=0,"",INDEX(Machine_Type,MATCH(A2,Machine_Number,0)))