Match criteria with an array of criteria
 

This is actually for Excel 2000.
I wish to return a value from a range of cells that each relate to a
differently named criteria, for one of those crieria.

cell1 - which will, I presume, hold the formula
cell2 - which contains criteria I am wishing to find a result for, e.g. C
range of (say) 5 cells named A to E
range of 5 cells which relate to the above sells and contain numerical
values, i.e. 1 to 5

That is, if both ranges are in logical sequence (they will not be), then the
formula should return a value of "3" when "C" is in the criteria cell (cell2)

I should be very grateful for suggestions, if it is possible.

Match criteria with an array of criteria
 

Is this what you want:

A1 = lookup value = C

...........B..........C
1........A.........10
2........B.........22
3........C.........17
4........D.........14
5........E..........12

The result you want is 17?

If so, try one of these:

=VLOOKUP(A1,B1:C5,2,0)

Or, if the value to be returned is numeric as in the example:

=SUMIF(B1:B5,A1,C1:C5)

--
Biff
Microsoft Excel MVP


"JohnB" wrote in message
...
This is actually for Excel 2000.
I wish to return a value from a range of cells that each relate to a
differently named criteria, for one of those crieria.

cell1 - which will, I presume, hold the formula
cell2 - which contains criteria I am wishing to find a result for, e.g. C
range of (say) 5 cells named A to E
range of 5 cells which relate to the above sells and contain numerical
values, i.e. 1 to 5

That is, if both ranges are in logical sequence (they will not be), then
the
formula should return a value of "3" when "C" is in the criteria cell
(cell2)

I should be very grateful for suggestions, if it is possible.

Match criteria with an array of criteria
 

Assuming your example goes left to right, formula in A1, criteria in B1:
=VLOOKUP(B1,C1:D5,2,FALSE)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"JohnB" wrote:

This is actually for Excel 2000.
I wish to return a value from a range of cells that each relate to a
differently named criteria, for one of those crieria.

cell1 - which will, I presume, hold the formula
cell2 - which contains criteria I am wishing to find a result for, e.g. C
range of (say) 5 cells named A to E
range of 5 cells which relate to the above sells and contain numerical
values, i.e. 1 to 5

That is, if both ranges are in logical sequence (they will not be), then the
formula should return a value of "3" when "C" is in the criteria cell (cell2)

I should be very grateful for suggestions, if it is possible.

Match criteria with an array of criteria
 

Brilliant.

I am pleased that I was able to convey my requirement so that someone
understood it and, particularly, that you were able to suggest a solution. I
did require a numeric result so the SUMIF formula was the one which worked.

Thanks again.

JohnB

"T. Valko" wrote:

Is this what you want:

A1 = lookup value = C

...........B..........C
1........A.........10
2........B.........22
3........C.........17
4........D.........14
5........E..........12

The result you want is 17?

If so, try one of these:

=VLOOKUP(A1,B1:C5,2,0)

Or, if the value to be returned is numeric as in the example:

=SUMIF(B1:B5,A1,C1:C5)

--
Biff
Microsoft Excel MVP


"JohnB" wrote in message
...
This is actually for Excel 2000.
I wish to return a value from a range of cells that each relate to a
differently named criteria, for one of those crieria.

cell1 - which will, I presume, hold the formula
cell2 - which contains criteria I am wishing to find a result for, e.g. C
range of (say) 5 cells named A to E
range of 5 cells which relate to the above sells and contain numerical
values, i.e. 1 to 5

That is, if both ranges are in logical sequence (they will not be), then
the
formula should return a value of "3" when "C" is in the criteria cell
(cell2)

I should be very grateful for suggestions, if it is possible.

Match criteria with an array of criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JohnB" wrote in message
...
Brilliant.

I am pleased that I was able to convey my requirement so that someone
understood it and, particularly, that you were able to suggest a solution.
I
did require a numeric result so the SUMIF formula was the one which
worked.

Thanks again.

JohnB

"T. Valko" wrote:

Is this what you want:

A1 = lookup value = C

...........B..........C
1........A.........10
2........B.........22
3........C.........17
4........D.........14
5........E..........12

The result you want is 17?

If so, try one of these:

=VLOOKUP(A1,B1:C5,2,0)

Or, if the value to be returned is numeric as in the example:

=SUMIF(B1:B5,A1,C1:C5)

--
Biff
Microsoft Excel MVP


"JohnB" wrote in message
...
This is actually for Excel 2000.
I wish to return a value from a range of cells that each relate to a
differently named criteria, for one of those crieria.

cell1 - which will, I presume, hold the formula
cell2 - which contains criteria I am wishing to find a result for, e.g.
C
range of (say) 5 cells named A to E
range of 5 cells which relate to the above sells and contain numerical
values, i.e. 1 to 5

That is, if both ranges are in logical sequence (they will not be),
then
the
formula should return a value of "3" when "C" is in the criteria cell
(cell2)

I should be very grateful for suggestions, if it is possible.