excel formula for selecting on large # of possiblities with 3 possible results

[erin7823]

Hello, this is my first post on anything so here goes.

<style type="text/css">P { margin-bottom: 0.08in; }</style> column G represents a range of values (i.e. 10 to 90) of which I'm trying to key on one of several possibilities; ( 12,13,19,24,60,61,62,63,64,65,66,67,68,or 72)



columns C and M represent values ($0.00 to $x.00), either C or M but not both may hold a value in the same row


as an example:
if column G=64 and C<>0, I would like column X to result in C's value;


if G=64 and C=0, I would like column X to result in M's value;


if column G does not match any of the selected values, column X should result in a blank


Thank you in advance for any assistance in this problem.
Erin

 

[StephenCrump]

Welcome to MrExcel!

Perhaps something like:

Excel 2010

	A	B	C	G	M	X
1	Target values		11	12		11
2	12				22
3	13		33	64		33
4	19				44
5	24
6	60
7	61
8	62
9	63
10	64
11	65
12	66
13	67
14	68
15	72

<tbody>
</tbody>

Sheet1​

Formula in X1: =IFERROR(IF(MATCH(G1,A$2:A$15,0),C1+M1),"") copied down the column.

This relies on either C or M being blank. I'd prefer to put in a check:

Formula in X1: =IFERROR(IF(MATCH(G1,A$2:A$15,0),IF(OR(C1=0,M1=0),C1+M1,"M and C are both non-zero")),"")

 

[erin7823]

Thanks for the quick followup Steven. I entered the formulas, but I didn't understand them and may not have entered them correctly. Any one of my range of numbers could appear in each row of column G. Does (g1,a%2:a$15,0) represnt an array? I apologize but my lack of experience at this point has melost. marshall

 

[StephenCrump]

Have a play with this: https://app.box.com/s/th2uhxc72rcrffmwuoop

Is it close to what you're looking for?

 

[erin7823]

Have a play with this: https://app.box.com/s/th2uhxc72rcrffmwuoop

Is it close to what you're looking for?

Steven, C and M never have >0 values in the same row, so the addition of C+M is fine. the selection of key #'s appears to work from your example. I don't understand how this formaula could be put into the X cell if this an array thing, my ignorance.

It is early am here in USA/MD. but I want to try and hang with you as long as I can, Marshall

an update Steven, when a key value is not found in G, X should be blank or zero

 

[StephenCrump]

an update Steven, when a key value is not found in G, X should be blank or zero

I think the spreadsheet I've posted does this? For example, G6 is 59, which is not in your list of keys, so X6 returns blank.

 

[erin7823]

I think the spreadsheet I've posted does this? For example, G6 is 59, which is not in your list of keys, so X6 returns blank.

THANK YOU Steven, It works beautifully.

• 
  
  Marshall