Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Match 2 criteria with 2 criteria | Excel Worksheet Functions | |||
Array with two criteria | Excel Discussion (Misc queries) | |||
match multiple criteria & return value from array | Excel Worksheet Functions |