Matching data from three columns (VLOOKUP, HLOOKUP, etc...)-URGENT
 

Hi All, Please help the due date is today (01/15) by the end of the day
I have series of data in excel spreadsheet (please see the below example)
and have to complete the following:
For each Application (A1)-Lookup by "Application Name" not by row#, I need
to match Object (B1) and its Rating (C1) to update another worksheet.

Application Object Rating
db1 Instances/Subsystems 14
db1 Tables 4305
db1 Indexes 5940
db1 Procedures 1380
db1 Triggers 488
db1 Constraints 573
db1 # of rows 35
db64 Instances/Subsystems 1
db64 Tables 5383
db64 Indexes 4728
db64 Procedures 119
db64 Triggers 31
db64 Constraints 2173
db64 # of rows 150
db51 Instances/Subsystems 2
db51 Tables 230
db51 Indexes 498
db51 Procedures 142
db51 Triggers 0
db51 Constraints 230
db51 # of rows 00

Result:
db1 Instances/Subsystems 14
Tables 4305
Indexes 5940
db64 Instances/Subsystems 1
Tables 5383
Indexes 4728
db51 Instances/Subsystems 2
Tables 230
Indexes 498
Thanks all in advance for fast reponses

Matching data from three columns (VLOOKUP, HLOOKUP,etc...)-URGENT
 

I don't fully understand what you are trying to do. Are you saying
that you have the first table of data, and that you want to complete
the following table where the ? is shown?:

db1 Instances/Subsystems ?
Tables ?
Indexes ?
db64 Instances/Subsystems ?
Tables ?
Indexes ?
db51 Instances/Subsystems ?
Tables ?
Indexes ?

Pete


On Jan 14, 11:06*pm, shulmany
wrote:
* Hi All, Please help the due date is today (01/15) by the end of the day
I have series of data in excel spreadsheet (please see the below example)
and have to complete the following:
For each Application (A1)-Lookup by "Application Name" not by row#, I need
to match Object (B1) and its Rating (C1) to update another worksheet.

Application * * Object * * * * * * * * * * Rating * * * * *
db1 * * Instances/Subsystems * * * 14 * * *
db1 * * Tables * * * * * * * * * * 4305 * *
db1 * * Indexes * * * * * * * * * *5940 * *
db1 * * Procedures * * * * 1380 * *
db1 * * Triggers * * * * * * * * * * * * * 488 * *
db1 * * Constraints * * * *573 * *
db1 * * # of rows * * * * * * * * * * * * *35 * * *
db64 * *Instances/Subsystems * * * *1 * * *
db64 * *Tables * * * * * * * * * * *5383 * * * * *
db64 * *Indexes * * * * * * * * * *4728 * *
db64 * *Procedures * * * * 119 * *
db64 * *Triggers * * * * * * * * * * * * * 31 * * *
db64 * *Constraints * * * *2173 * *
db64 * *# of rows * * * * * * * * * * * * *150 * *
db51 * *Instances/Subsystems * * * *2 * * *
db51 * *Tables * * * * * * * * * * 230 * *
db51 * *Indexes * * * * * * * * * 498 * * *
db51 * *Procedures * * * *142 * * *
db51 * *Triggers * * * * * * * * * * * * *0 * * * *
db51 * *Constraints * * * 230 * * *
db51 * *# of rows * * * * * * * * * * * *00

Result:
db1 * * Instances/Subsystems * * * 14 * * *
* * * * Tables * * * * * * * * * * 4305 * *
* * * * Indexes * * * * * * * * * *5940
db64 * *Instances/Subsystems * * * *1 * * *
* * * * Tables * * * * * * * * * * *5383 * * * * *
* * * * Indexes * * * * * * * * * *4728
db51 * *Instances/Subsystems * * * *2 * * *
* * * * Tables * * * * * * * * * * 230 * *
* * * * Indexes * * * * * * * * * 498 * *
Thanks all in advance for fast reponses

Matching data from three columns (VLOOKUP, HLOOKUP, etc...)-URGENT
 

Assuming source data as posted is in Sheet1, cols A to C

In Sheet2,
you have this repeating pattern set-up running in A2:B2 down:

db1 Instances/Subsystems
Tables
Indexes
db64 Instances/Subsystems
Tables
Indexes
etc

Put in C2, normal ENTER:
=INDEX(Sheet1!C$2:C$100,MATCH(1,INDEX((Sheet1!A$2: A$100=INDIRECT("A"&(INT((ROWS($1:1)-1)/3)+1)*3-1))*(Sheet1!B$2:B$100=B2),),0))
Copy C2 down as far as required to return the required results. Adapt the
ranges to suit your actuals in Sheet1.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"shulmany" wrote:
Hi All, Please help the due date is today (01/15) by the end of the day
I have series of data in excel spreadsheet (please see the below example)
and have to complete the following:
For each Application (A1)-Lookup by "Application Name" not by row#, I need
to match Object (B1) and its Rating (C1) to update another worksheet.

Application Object Rating
db1 Instances/Subsystems 14
db1 Tables 4305
db1 Indexes 5940
db1 Procedures 1380
db1 Triggers 488
db1 Constraints 573
db1 # of rows 35
db64 Instances/Subsystems 1
db64 Tables 5383
db64 Indexes 4728
db64 Procedures 119
db64 Triggers 31
db64 Constraints 2173
db64 # of rows 150
db51 Instances/Subsystems 2
db51 Tables 230
db51 Indexes 498
db51 Procedures 142
db51 Triggers 0
db51 Constraints 230
db51 # of rows 00

Result:
db1 Instances/Subsystems 14
Tables 4305
Indexes 5940
db64 Instances/Subsystems 1
Tables 5383
Indexes 4728
db51 Instances/Subsystems 2
Tables 230
Indexes 498
Thanks all in advance for fast reponses

Matching data from three columns (VLOOKUP, HLOOKUP, etc...)-UR
 

Hi, yes it is true. I need to match data from the first table to another
worksheet in different format.
thanks

"Pete_UK" wrote:

I don't fully understand what you are trying to do. Are you saying
that you have the first table of data, and that you want to complete
the following table where the ? is shown?:

db1 Instances/Subsystems ?
Tables ?
Indexes ?
db64 Instances/Subsystems ?
Tables ?
Indexes ?
db51 Instances/Subsystems ?
Tables ?
Indexes ?

Pete


On Jan 14, 11:06 pm, shulmany
wrote:
Hi All, Please help the due date is today (01/15) by the end of the day
I have series of data in excel spreadsheet (please see the below example)
and have to complete the following:
For each Application (A1)-Lookup by "Application Name" not by row#, I need
to match Object (B1) and its Rating (C1) to update another worksheet.

Application Object Rating
db1 Instances/Subsystems 14
db1 Tables 4305
db1 Indexes 5940
db1 Procedures 1380
db1 Triggers 488
db1 Constraints 573
db1 # of rows 35
db64 Instances/Subsystems 1
db64 Tables 5383
db64 Indexes 4728
db64 Procedures 119
db64 Triggers 31
db64 Constraints 2173
db64 # of rows 150
db51 Instances/Subsystems 2
db51 Tables 230
db51 Indexes 498
db51 Procedures 142
db51 Triggers 0
db51 Constraints 230
db51 # of rows 00

Result:
db1 Instances/Subsystems 14
Tables 4305
Indexes 5940
db64 Instances/Subsystems 1
Tables 5383
Indexes 4728
db51 Instances/Subsystems 2
Tables 230
Indexes 498
Thanks all in advance for fast reponses

Matching data from three columns (VLOOKUP, HLOOKUP, etc...)-UR
 

So far this works only in the same format and applications name.
It doesn't work if appl name(A) and/or format are different in sheet2 and if
new application is added to sheet1. I believe, that refernce to Application
Name should be added into the function. Any solution.
Thanks

"Max" wrote:

Assuming source data as posted is in Sheet1, cols A to C

In Sheet2,
you have this repeating pattern set-up running in A2:B2 down:

db1 Instances/Subsystems
Tables
Indexes
db64 Instances/Subsystems
Tables
Indexes
etc

Put in C2, normal ENTER:
=INDEX(Sheet1!C$2:C$100,MATCH(1,INDEX((Sheet1!A$2: A$100=INDIRECT("A"&(INT((ROWS($1:1)-1)/3)+1)*3-1))*(Sheet1!B$2:B$100=B2),),0))
Copy C2 down as far as required to return the required results. Adapt the
ranges to suit your actuals in Sheet1.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"shulmany" wrote:
Hi All, Please help the due date is today (01/15) by the end of the day
I have series of data in excel spreadsheet (please see the below example)
and have to complete the following:
For each Application (A1)-Lookup by "Application Name" not by row#, I need
to match Object (B1) and its Rating (C1) to update another worksheet.

Application Object Rating
db1 Instances/Subsystems 14
db1 Tables 4305
db1 Indexes 5940
db1 Procedures 1380
db1 Triggers 488
db1 Constraints 573
db1 # of rows 35
db64 Instances/Subsystems 1
db64 Tables 5383
db64 Indexes 4728
db64 Procedures 119
db64 Triggers 31
db64 Constraints 2173
db64 # of rows 150
db51 Instances/Subsystems 2
db51 Tables 230
db51 Indexes 498
db51 Procedures 142
db51 Triggers 0
db51 Constraints 230
db51 # of rows 00

Result:
db1 Instances/Subsystems 14
Tables 4305
Indexes 5940
db64 Instances/Subsystems 1
Tables 5383
Indexes 4728
db51 Instances/Subsystems 2
Tables 230
Indexes 498
Thanks all in advance for fast reponses

Matching data from three columns (VLOOKUP, HLOOKUP, etc...)-UR
 

First, just to clarify that the earlier expression is flexible wrt col B.

Ok, since now your actual scenario is clarified, ie the number of successive
rows in-between items in col A may differ (not necessarily 3), I'd suggest
this set-up.

In Sheet2,
Use a helper col C to completely fill the in-between blanks in col A
irrespective of the number of lines between items
In C2: =A2
In C3: =IF(A3="",C2,A3)
Copy C3 down to the last row of data in col B, to produce a completely
filled replica of col A.

Then just place in D2, normal ENTER:
=INDEX(Sheet1!C$2:C$100,MATCH(1,INDEX((Sheet1!A$2: A$100=C2)*(Sheet1!B$2:B$100=B2),),0))
Copy down for the required results

Pl mark ALL responses which help by pressing the YES buttons (like the ones
below)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"shulmany" wrote:
So far this works only in the same format and applications name.
It doesn't work if appl name(A) and/or format are different in sheet2 and if
new application is added to sheet1. I believe, that refernce to Application
Name should be added into the function. Any solution.
Thanks