Match criteria with an array of criteria

J

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

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)
 
L

Luke M

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top