Excel-number comparison 1

[copeco]

Good morning. eg. There is a number input in cells A1,B1 & C1. There is a range of cells E1:H20. I'm wanting to compare the input cells to the range cells. If there is a match print the matching #s in D1?. pls help...thanks

 

[Hasit]

Put this formula in cell D1 and then hit CTRL-SHIFT-ENTER so that you get the curly brackets around the formula:

=SUM(IF($E$1:$H$20=A1,A1,0))

Once you have done that, drag the formula down to D2 and D3 and that should give you the matching numbers.

HTH.

 

[copeco]

The Sum(IFxxx) works well except if the same number shows again cell D1 adds it again. I tried taking the SUM out but only get a "TRUE". I'm in hopes of just showing the number in D1 just once & not adding to itself..thanks much

 

[beniez]

There may be a better way of doing this, but I think you need to use MATCH(). Unfortunately, MATCH() seems only to work on a two-dimensional array, so you need to AND results for each of your columns (E2:E20, F2:F20, etc), thus:

=IF(AND(ISNA(MATCH(A1,E1:E20,0)),ISNA(MATCH(A1,F1:F20,0))),0,A1)

I've only included the first two columns; I leave putting the rest in as an exercise for you!

HTH

Ben

 

[beniez]

Curses... why do my best ideas always come just after I've pressed the submit button??

Try:

=IF(COUNTIF(E1:H20,A1)>0,A1,0)

Regards

Ben

 

[copeco]

Thanks so much for the help. I modified the line a little:
=COUNTIF(E1:H20,A1). I changed the background color & bolded the number. This helps for easy recognition...thanks again !!