When you type in the formula, instead of hitting Enter hold down the Control
and Shift keys, then hit Enter. Excel will put braces { } around the
formula (indicating it is an array formula). It does not work with entire
columns, such as A:A or A1:A65536 (for versions prior to 2007), but you could
use A1:A65535

For example:

=INDEX(Sheet1!F1:F65535, MATCH(ValueA&" "&ValueB, Sheet1!C1:C65535&"
"&Sheet1!D1:D65535, 0))

More discussion he
http://cpearson.com/excel/array.htm

Depending on how many formulae you need and how large the ranges are, array
formulae could adversely affect performance, so there is a trade off that
will need to be evaluated.


"Monish" wrote:

Thanks guys - I will try both ways...I am trying to establish an automated
process where they would simply paste in Sheet 1 data from a database output
I am setting up for them and the report tabs would be populated...so I may
hard code and protect the concatenated column?
...also, JMB: what do you mean by "commited with Cntrl+Shift+Enter"?

"JMB" wrote:

Another possibility:
=INDEX(Sheet1!F1:F1500, MATCH(ValueA&" "&ValueB, Sheet1!C1:C1500&"
"&Sheet1!D1:D1500, 0))

commited with Cntrl+Shift+Enter

"Monish" wrote:

I have Sheet 1 with 1500 rows and 20 columns

On Sheet 2 I am trying to create a report where one row/cell returns:
Value in Column F from Sheet 1, but ONLY WHEN
Column C in Sheet 1 = Value A, AND
Column D in Sheet 2 = Value B

There will only be one row (among 1500) which meets both criteria, but I
cant think of how to call it and get another cell in that row to be returned.

I cant use a macro / VB because my client has an older version of Excel and
it will not take.

Appreciate any help!