Match criteria with an array of criteria

[JohnB]

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.

 

[T. Valko]

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)

 

[Luke M]

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

 

[JohnB]

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]

You're welcome. Thanks for the feedback!