Multiple IF's in one cell

  • Thread starter Thread starter Michael Morin
  • Start date Start date
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.
 
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
 
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?
 
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.
 
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.
 
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.
 
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))),"")
 
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))),"")
 
Back
Top