excel filter formula without v-lookup

77 views Asked by At

I have the below table and I am trying to make a formula that would search for all cells in column A that begin with 240 and return the whole row back. I have tried to use V-lookup but that didnt work, any other ideas?:

240-11001   a   1   rg
240-11201   a   2   en
240-11430   a   3   de
240-11980   a   4   sp
242-11001   b   9   fr
242-11201   b   8   fr
242-11430   b   7   en
242-11980   b   6   us
1

There are 1 answers

0
kojow7 On

You weren't completely clear how you wanted the row to be displayed, if you want the row to be displayed in a single cell you could do it this way:

=IF(LEFT(A1, 3) = "240", CONCATENATE(A1, " ", B1, " ", C1, " ", D1), "")

If you want each column to display in separate cells, then you'll have to repeat it in each cell:

=IF(LEFT(A1, 3) = "240", A1, "")
=IF(LEFT(A1, 3) = "240", B1, "")
=IF(LEFT(A1, 3) = "240", C1, "")
=IF(LEFT(A1, 3) = "240", D1, "")