Multiple IF's in one cell

M

Michael Morin

I would like to find a formula that will allow me to sort/find a name in
several columns of names and if it is one of the columns then indicate which
column heading it was under. I've tried embedded IFs but I have limited
experience with using multiple formulas in one cell.
 
B

barry houdini

Hello Michael,

Are you searching for the name in a single row or several rows?

If it's the former then assuming you want to search in A2:J2 and
return the heder from A1:J1 try

=INDEX(A$1:J$1,MATCH(L2,A2:J2,0))

where L2 contains the name for which you need to search

for the latter try this to search within rows 2 to 10

=IF(COUNTIF(A2:J10,L2),INDEX(A$1:J$1,MIN(IF(A2:J10=L2,COLUMN(A2:J10)-
COLUMN(A2)+1))),"")

This second formula is an "array formula" which needs to be confirmed
with CTRL+SHIFT+ENTER so that curly braces appear around the formula
in the formula bar
 
M

Michael Morin

Thanks for your help.

I have, for example, counties in one row and under them I have cities and
towns in those counties. So, really I have columns of info. You asked about
rows. Does it have to be in rows or can I use columns instead?
 
B

barry houdini

Thanks for your help.

I have, for example, counties in one row and under them I have cities and
towns in those counties.  So, really I have columns of info. You asked about
rows.  Does it have to be in rows or can I use columns instead?









- Show quoted text -
Click to expand...

Hello Michael,

Yes, I realised you had columns of data but what I wasn't sure of was
whether you just had a single row to search or multiple rows. It looks
like you have multiple rows so if you have counties in A1:J1 and towns/
cities below those in A2:J10 then you can use the second formula I
suggested, i.e.

=IF(COUNTIF(A2:J10,L2),INDEX(A$1:J$1,MIN(IF(A2:J10=L2,COLUMN(A2:J10)-
COLUMN(A2)+1))),"")

which needs to be confirmed with CTRL+SHIFT+ENTER.

Ultimately it would probably be easier for you if you could change
your layout so that you had a single column of all towns/cities and
the adjacent column contained the relevant County, then you can jsut
do a simple VLOOKUP.
 
M

Michael Morin

oh ok :) Thank you! I'll let you know how it turns out.

Thanks for your help.

I have, for example, counties in one row and under them I have cities and
towns in those counties. So, really I have columns of info. You asked
about
rows. Does it have to be in rows or can I use columns instead?









- Show quoted text -
Click to expand...

Hello Michael,

Yes, I realised you had columns of data but what I wasn't sure of was
whether you just had a single row to search or multiple rows. It looks
like you have multiple rows so if you have counties in A1:J1 and towns/
cities below those in A2:J10 then you can use the second formula I
suggested, i.e.

=IF(COUNTIF(A2:J10,L2),INDEX(A$1:J$1,MIN(IF(A2:J10=L2,COLUMN(A2:J10)-
COLUMN(A2)+1))),"")

which needs to be confirmed with CTRL+SHIFT+ENTER.

Ultimately it would probably be easier for you if you could change
your layout so that you had a single column of all towns/cities and
the adjacent column contained the relevant County, then you can jsut
do a simple VLOOKUP.
 
M

Michael Morin

Well, it works WONDERFULLY! The only thing is that I have to go back and
edit the range of the columns to find. It keeps bumping them down by one
for proceding formulas.


Thanks for your help.

I have, for example, counties in one row and under them I have cities and
towns in those counties. So, really I have columns of info. You asked
about
rows. Does it have to be in rows or can I use columns instead?









- Show quoted text -
Click to expand...

Hello Michael,

Yes, I realised you had columns of data but what I wasn't sure of was
whether you just had a single row to search or multiple rows. It looks
like you have multiple rows so if you have counties in A1:J1 and towns/
cities below those in A2:J10 then you can use the second formula I
suggested, i.e.

=IF(COUNTIF(A2:J10,L2),INDEX(A$1:J$1,MIN(IF(A2:J10=L2,COLUMN(A2:J10)-
COLUMN(A2)+1))),"")

which needs to be confirmed with CTRL+SHIFT+ENTER.

Ultimately it would probably be easier for you if you could change
your layout so that you had a single column of all towns/cities and
the adjacent column contained the relevant County, then you can jsut
do a simple VLOOKUP.
 
B

barry houdini

The only thing is that I have to go back and
edit the range of the columns to find.  It keeps bumping them down by one
for proceding formulas.
Click to expand...

OK, sorry, yes, you can fix the ranges with dollar signs so that only
the L2 reference will change as you drag down or across, i.e.

=IF(COUNTIF($A$2:$J$10,L2),INDEX($A$1:$J$1,MIN(IF($A$2:$J$10=L2,COLUMN
($A$2:$J$10)-
COLUMN($A$2)+1))),"")
 
M

Michael Morin

EXCELLENT ... You've made my job so much easier. Now, I can streamline what
I did for next month's reports, I'll be all set! Thank you again!

The only thing is that I have to go back and
edit the range of the columns to find. It keeps bumping them down by one
for proceding formulas.
Click to expand...

OK, sorry, yes, you can fix the ranges with dollar signs so that only
the L2 reference will change as you drag down or across, i.e.

=IF(COUNTIF($A$2:$J$10,L2),INDEX($A$1:$J$1,MIN(IF($A$2:$J$10=L2,COLUMN
($A$2:$J$10)-
COLUMN($A$2)+1))),"")
 
Top