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!

You could use a "helper" column on Sheet1 containing the concatenated
content of columns C and D.
Then use vlookup on that column.

Tim


"Monish" wrote in message
...
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!

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!

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!

I Kinda need the same thing i use excel 2002
the only problem is ctrl+alt+enter never works for me , i need to enable it
somewhere first ?


"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!

No. It is built in.

--
Regards,
Tom Ogilvy


"azoz" wrote in message
...
i meant ctrl+shift+enter

"azoz" wrote:

I Kinda need the same thing i use excel 2002
the only problem is ctrl+alt+enter never works for me , i need to enable
it
somewhere first ?


"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!

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!

What do I need if there are multiple rows which will meet the criteria and I
want the sum of each of these rows' value from column F?

Thanks!

"Tim Williams" wrote:

You could use a "helper" column on Sheet1 containing the concatenated
content of columns C and D.
Then use vlookup on that column.

Tim


"Monish" wrote in message
...
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!