Index Match Formula VBA

I am looking for a formula to put the "status" of a project from the worksheet Matrix (2) in the right row of the reference table (1). I tried index match but could not solve the problem.

In the ws (1) as shown below there is my table with about 50000 rows and would like automate the task with vba.

In ws 1, in Col A I have the #ID and the date in Col B.
In ws 2 with the Matrix I have the #ID in Col A and the dates in row 1. I would like transfer Col C from Matrix to the Reference Table in Col C if date and ID matches.

(1) Reference Table

(2) Matrix

Any assistance will be greatly appreciated.

Thank you.

Regards,

Adam
Sample.xlsx

VB ScriptMicrosoft OfficeVBAMicrosoft ExcelSpreadsheets

Last Comment

Shums Faruk

8/22/2022 - Mon

Shums Faruk

3/24/2017

Hi Adam,

First prepare 3 Name Range for Tabelle1 Sheet.
KM_Port:
=OFFSET(Tabelle1!$A$2,0,0,COUNT(IF(Tabelle1!$A$2:$A$10000="","",1)),1)
Datum:
=OFFSET(Tabelle1!$B$2,0,0,COUNT(IF(Tabelle1!$B$2:$B$10000="","",1)),1)
Target:
=OFFSET(Tabelle1!$C$2,0,0,COUNT(IF(Tabelle1!$C$2:$C$10000="","",1)),1)

Then in Tabelle2 apply below Array formula confirmed with Ctrl+Shift+Enter and drag through columns and rows:
=IFERROR(INDEX(Target,MATCH(1,($A2=KM_Port)*(B$1=Datum),0),1),"")

As your details for Target Column In Tabelle1 is incomplete, it cannot fill the details in Tabelle2.

Please find attached...

If you still wants me to create a VBA, please fill the details for Target
Adam-Sample_v1.xlsx

Adam Elsheimer

3/24/2017

ASKER

I am so sorry for my incorrect explanation. But I would like transfer the values from Tabelle to Tabelle 1.

Thank you for all your help the last days.

God bless

Regards

Adam

Shums Faruk

3/25/2017

Hi Adam,

Sorry for misunderstanding, please use below formula in Tabelle1 for Target Column:
=IFERROR(INDEX(Tabelle2!$B$2:$H$9,MATCH($A2,KM_Port,0),MATCH($B2,Tabelle2!$B$1:$H$1,0)),"")
You need to change range as per your original workbook, like:
1. Tabelle2!$B$2:$H$9 (change accordingly as per your data in Tabelle2)
2. For KM_Port I have created a Name Range for 10000 rows, if you need longer please change this formula as per your need: =OFFSET(Tabelle2!$A$2,0,0,COUNT(IF(Tabelle2!$A$2:$A$10000="","",1)),1)
Tabelle2!$A$2:$A$10000 (change accordingly as per Column A in Tabelle2)
3. Tabelle2!$B$1:$H$1 (change accordingly as per dates columns in row 1 in Tabelle2)

Hope it helps.

Please find attached for your reference.
Adam-Sample_v2.xlsx

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

ASKER CERTIFIED SOLUTION

Shums Faruk

3/25/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts