Index Match/Populate data based on key value

Hello All, I am not too familiar with VBA, but I am hoping you can help me with the following problem. I have a primary spreadsheet that is posted by Infrastrcture team on a weekly basis that I download from a sharepoint site.
Every week after getting the new file, I use the following INDEX and MATCH function to match values based on CurrentHostName. If the CurrentHostName matches, values are populated my own spreadsheet from Column "C" through "N". Column "T" through "Z" are updated based on my interaction with the customers.

=INDEX('[InfrastrctureData.xls]Server Status'!$A$1:$CQ$65000,MATCH(B2,'[InfrastrctureData.xls]Server Status'!$A$1:$A$65000,0),10)

1) Automatically assign a new record ID in column "A" based on highest cell value +1.
2) Based on "CurrentHostName" that will be entered, populate and search all the columns from "C" through "N".

Thank you in advance, attached is the example sheet I am working with.

I am not sure why the attachment did not come thru. I am trying again.

selected data for avg matching buttons.xlsm

This file may help with your question. Look at the functions that I use to populate the data. I use the functions: ifError, index and match.

Please Login or Register  to view this content.

For each line click (shift + control + enter). This button utilizes this function in my code. This button also formats the excel spreadsheet with the use of a recorded macro. If you have more questions let me know.

Thanks

Thank you Rmachbitz. I appreciate you taking the time and taking a look at this issue. Your code helped me get on the right track and come up with desired solution. Just one quick question, if you can ellaborate on the match function here, that would be great. What I understood is that you are trying to match, F4, I3, G4 with an and clause from three arrarys, but does that mean if what if there are more than one match, would IFERROR still return just one row?

I was trying to match the x, y, and z coordinates to a different location so I used the match function. The AND clause was used for x, and y, and z. See how I have to use an AND clause to get all three coordinates. When your code is fixed post it, so others can see it. If you need more help, let me know.

Thank you RmachBitz.

It works, here it is, even thought it is slow since, I am looking through a file with 8000 entries.

Sub NoETARef()
Dim RCnt As Long

RCnt = Range("A65536").End(xlUp).Row
Range("C2").Select
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-1],'[InfData.xls]Server Status'!R1C1:R8000C1,0),47)"
Selection.AutoFill Destination:=Range("C2:C" & RCnt), Type:=xlFillDefault
Range("C2" & RCnt).Select

Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-2],'[InfData.xls]Server Status'!R1C1:R8000C1,0),2)"
Selection.AutoFill Destination:=Range("D2:D" & RCnt), Type:=xlFillDefault
Range("D2" & RCnt).Select

Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-3],'[InfData.xls]Server Status'!R1C1:R8000C1,0),4)"
Selection.AutoFill Destination:=Range("E2:E" & RCnt), Type:=xlFillDefault
Range("E2" & RCnt).Select

Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-4],'[InfData.xls]Server Status'!R1C1:R8000C1,0),13)"
Selection.AutoFill Destination:=Range("F2:F" & RCnt), Type:=xlFillDefault
Range("F2" & RCnt).Select


Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-5],'[InfData.xls]Server Status'!R1C1:R8000C1,0),26)"
Selection.AutoFill Destination:=Range("G2:G" & RCnt), Type:=xlFillDefault
Range("G2" & RCnt).Select


Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-6],'[InfData.xls]Server Status'!R1C1:R8000C1,0),18)"
Selection.AutoFill Destination:=Range("H2:H" & RCnt), Type:=xlFillDefault
Range("H2" & RCnt).Select


Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C95,MATCH(RC[-7],'[InfData.xls]Server Status'!R1C1:R8000C1,0),91)"
Selection.AutoFill Destination:=Range("I2:I" & RCnt), Type:=xlFillDefault
Range("I2" & RCnt).Select
Columns("I:I").Select
Selection.NumberFormat = "mm/dd/yy;@"


Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-8],'[InfData.xls]Server Status'!R1C1:R8000C1,0),8)"
Selection.AutoFill Destination:=Range("J2:J" & RCnt), Type:=xlFillDefault
Range("J2" & RCnt).Select
Columns("J:J").Select
Selection.NumberFormat = "mm/dd/yy;@"

Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-9],'[InfData.xls]Server Status'!R1C1:R8000C1,0),24)"
Selection.AutoFill Destination:=Range("K2:K" & RCnt), Type:=xlFillDefault
Range("K2" & RCnt).Select


Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-10],'[InfData.xls]Server Status'!R1C1:R8000C1,0),25)"
Selection.AutoFill Destination:=Range("L2:L" & RCnt), Type:=xlFillDefault
Range("L2" & RCnt).Select

Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-11],'[InfData.xls]Server Status'!R1C1:R8000C1,0),34)"
Selection.AutoFill Destination:=Range("M2:M" & RCnt), Type:=xlFillDefault
Range("M2" & RCnt).Select

Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-12],'[InfData.xls]Server Status'!R1C1:R8000C1,0),10)"
Selection.AutoFill Destination:=Range("N2:N" & RCnt), Type:=xlFillDefault
Range("N2" & RCnt).Select


Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-13],'[InfData.xls]Server Status'!R1C1:R8000C1,0),38)"
Selection.AutoFill Destination:=Range("O2:O" & RCnt), Type:=xlFillDefault
Range("O2" & RCnt).Select

Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-14],'[InfData.xls]Server Status'!R1C1:R8000C1,0),37)"
Selection.AutoFill Destination:=Range("P2:P" & RCnt), Type:=xlFillDefault
Range("P2" & RCnt).Select

Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-15],'[InfData.xls]Server Status'!R1C1:R8000C1,0),35)"
Selection.AutoFill Destination:=Range("Q2:Q" & RCnt), Type:=xlFillDefault
Range("Q2" & RCnt).Select

Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-16],'[InfData.xls]Server Status'!R1C1:R8000C1,0),36)"
Selection.AutoFill Destination:=Range("R2:R" & RCnt), Type:=xlFillDefault
Range("R2" & RCnt).Select

Range("S2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('[InfData.xls]Server Status'!R1C1:R8000C52,MATCH(RC[-17],'[InfData.xls]Server Status'!R1C1:R8000C1,0),7)"
Selection.AutoFill Destination:=Range("S2:S" & RCnt), Type:=xlFillDefault
Range("S2" & RCnt).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Range("A2").Select
End Sub

Great, glad I could help. You should put [code] tags around your code...that is a forum rule. If this thread is solved --> mark it as solved.

I will do that in the future. Thank you once again. I am marking this thread complete.