Scanning criteria of one worksheet against another and, if met, return certain data

Hi,

I have a model that has three workbooks. Workbook 1 and 2 contains different data sets – there are only two columns in worksheet 1 that can be matched to worksheet 2. Worksheet 3 is my summary page where i am displaying results.

I do require the data be tested for conditions – that is, I am only interested in a row in Worksheet 2 that has two specific columns that match two specific columns in a row in Worksheet 1. I want to view a certain cell of each of these rows where the condition has been proved.

I have overcome similar problems in the past using:
=INDEX(tbl, SMALL(IF(COUNTIFS($G$3, $B$3:$B$19, $G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER.
However, this model requires greater flexibility in criteria. Essentially i require for each row in worksheet 2 to be scanned against worksheet 1 and, when the above criteria is satisfied, display a cell from that matching row.

Imagine that each of the below described tables are in different worksheets. This is only a snapshop of the worksheets.

Worksheet 1:
includes several columns; of interest is column 3 and 5, being 'Tenant' and 'Building'.

Worksheet 2:
includes several columns; of interest column 2 and 8, being 'Tenant' and 'Building'.

Worksheet 3:
Results.

Essentially, i want every row of worksheet 1 checked against worksheet 2 and where there are two rows that have identical tenant and building, i want certain data of worksheet 1 returned to worksheet 3.

This is required as i need data from one worksheet, however the other contains critical search data.

I also require an additional screening - that is, i do not require any data from worksheet 1 (even if both previous fields have been met) if the expiry is greater than six months away.

Can anyone propose some formulas that could retrieve what i need?


All help is greatly appreciated. It has many people at my firm stumped!

How do i post a file so i can show you.

Hi, thanks for your help.

Please see attached an excel file, which displays my problem.

Please note that matching data will not be in the same rows on each worksheet.

It is also important to note that if there is where there has been a match found in worksheet 1 and 2 for ‘tenant name’ and ‘premises’, this will be unique. In such an event, I would like other data returned from worksheet one, like what has been done in your first model.

Also, as I am a naive excel user, why does this type of syntax require “Ctrl + Alt + Enter” to perform its function. Why won’t it work without it? What is so different in function about an ‘array’ formula?

I essentially want my worksheet 3 to be the summary where i can then sort by various metrics, like date, etc. If there are lines that do not have a match, then it should just return "no match" or something similar to an 'if' function returning various results... Unless you can propose something more elegant.

Ctrl + Alt + Enter lets excel know you what to perform functions on whole arrays. From the Microsoft website: An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result.

http://office.microsoft.com/en-us/ex...001087290.aspx

Essentially, i would like each row of worksheet 2 to be checked against each row of worksheet 1. The first match in the spreadsheet is a3 (row 10, worksheet 2) to a3 (row 2, worksheet 1). In such an instance, i would like other all other data from that row (worksheet 1) to appear in worksheet 3.
You will note that the rows will not always be the same. Therefore, worksheet 3 may have to show errors or a controlled message where the rows do not match.