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

  • Thread starter Thread starter shulmany
  • Start date Start date
S

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
 
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
 
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
 
Hi, yes it is true. I need to match data from the first table to another
worksheet in different format.
thanks
 
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
 
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
 
Back
Top