View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
shulmany shulmany is offline
external usenet poster
 
Posts: 3
Default 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