If formula to match a list of numbers to another list

**Robotacha2010** · 01-19-2011, 10:41 AM

Hello,

I have a spreadsheet.

column A is ACCOUNT NUMBERS ie; 3TEST, 3 GERRY , 1CART

Columns B to G are the tradlists for these customers ie, B1 = 406, C1 = 23.

tradelist values are 0 upto 999.

I need to identify which accounts have a certain tradelist in any of these 6 columns, and replace them with a 0

the tradelists I need to identify are:

407
408
409
410
411
412
413
414
415
416
425
426
434
435
436
437
438
439
440
442
443
445
446
447

I have done a formula in column H, which is

=IF(B2=407,"YES",IF(C2=407,"YES",IF(D2=407,"YES",IF(E2=407,"YES",IF(F2=407,"YES",IF(G2=407,"YES","NO))))))

which is fine to identify 1 number...

Can anyone suggest a way of identifying customers who have ANY of the tradelists in the 6 columns?

I was thinking along the lines of =IF(B2=407:447 etc.... and doing a range, but the numbers I need to find are not consistent....

Is there away of searching for multiple numbers with an IF statement?

I eargly await your replies :D

Thanks.

**NBVC** · 01-19-2011, 10:48 AM

Try something like:

=IF(SUMPRODUCT(COUNTIF($B1:$G1,$J$1:$J$24)),"Yes","No")

where J1:J24 contains the list of numbers to look for.

Then copy down.

**Robotacha2010** · 01-19-2011, 10:54 AM

Awesome!

worked perfectly.

as you can imagine, with 27000 customers, this has saved me ALOT of time.

I wish I had your brain or logical thinking!!

Massive thank-you's from me!

**Robotacha2010** · 01-19-2011, 11:02 AM

Actually, 1 more quick question...

as there is so many customers, I have filtered down to about 3000 rows now with them customers with them particular tradlists...

is there any way of changing all the cells that contain them numbers to a zero?

**NBVC** · 01-19-2011, 11:10 AM

One way would be to temporarily use another 6 columns to show results.

Eg... In a new column, say H2, enter:

=IF(ISNUMBER(MATCH(B2,$X$1:$X$24,0)),0,B2) copied across 6 columns

Then copy down... (where now X1:X25 contains the list of lookup numbers)

Then you can copy this new range of results, and go to B2, then Edit|Paste Special and select Values... then you can delete all those formula columns.