Need Help

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have a column with the following data. I would like to
use an IF statement that will return values from rows
within this column that contain an 8 or a 7 or a L (any or
all). Is this possible?


8
HQ2394
HL3
H
CDHLQKN3428
HQ234
CDNQ3589
HLQKN342
HQKN342
HKLQ234
HQ28
HL
H8
D98
CDQKN3429
LQKN34289
3
3
3
3
DQKN3429L
KN34
LQKN34289
LKN348AU167RWX
L
L
L
L
L
AU16RWXKNO34
AU16RWXKNO34
5KNO34
5KNO34
AUXW167R
AUXW167R
AUXW167R
HQKN3429
DLKN34
LKN3478
KN348
8
 
James,

Here is a formula that should work for you...

=INDEX(A$2:A$42,SMALL(IF((IF(ISNUMBER(SEARCH("8",A$2:A$42)),B$2:B$42,""))
<>"",IF(ISNUMBER(SEARCH("8",A$2:A$42)),B$2:B$42,""),IF(IF(ISNUMBER(SEARCH
("7",A$2:A$42)),B$2:B$42,"")<>"",IF(ISNUMBER(SEARCH("7",A$2:A$42)),B$2:B$42,"")
,IF(ISNUMBER(SEARCH("L",A$2:A$42)),B$2:B$42,""))),ROW()))

Lets say your data started in A1 then in B1 put a 1 in B2 a 2 in B3 a 3 etc...

Put this formula in C1 and array enter (control + shift + enter) (if it's done right { } will
appear around your formula.

Drag this down until you start getting #NUM!'s

If your data doesn't start in row 1 you'll need to modify the formula like
=INDEX(A$2:A$42,SMALL(IF((IF(ISNUMBER(SEARCH("8",A$2:A$42)),B$2:B$42,""))
<>"",IF(ISNUMBER(SEARCH("8",A$2:A$42)),B$2:B$42,""),IF(IF(ISNUMBER(SEARCH
("7",A$2:A$42)),B$2:B$42,"")<>"",IF(ISNUMBER(SEARCH("7",A$2:A$42)),B$2:B$42,"")
,IF(ISNUMBER(SEARCH("L",A$2:A$42)),B$2:B$42,""))),ROW()-X))

NOTE THE END ROW() - X (X is the number of rows above your starting point) so if you
were in row 2 it'd be ROW() - 1, in row 10 ROW() - 9

If you need a sample sheet or something e-mail me
 
Back
Top