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

[shulmany]

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

 

[Pete_UK]

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

 

[Max]

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]

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

 

[shulmany]

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]

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