Nest IF function

[Len]

Hi,

How to set a nested IF function to check column D for one or more
specific texts ,if matches it returns the corresponding value from
column J and go on the next row until it reaches the furnishing line
for example,
Column D Column J Column L

1) …travel… 200.00
IF(ISNUMBER(FIND("travel",$D1)),$J1,"") = 200.00
2) …air/fares 400.00
IF(ISNUMBER(FIND("travel",$D1)),$J1,"") = blank
3) …tolls… 50.00
IF(ISNUMBER(FIND("tolls",$D2)),$J2,"") = 50.00
4) …B’trips…travel… 124.00 Nested IF
formula ??? = 124.00
5) …B’trips…travel… tolls.. 75.00 Nested IF formula ??? =
75.00

For item 4), Nested IF formula for one or more specific texts ( ie
B'trip or travel )
For item 5), Nested IF formula for one or more specific texts ( ie
B'trip or travel or tolls )

Is it possible to set the above function such as like or contain to
check for one or more specific text from that row ?

Please help, many thanks

Regards
Len

 

[T. Valko]

Try this...

Make a list of the words you want to look for. Assume this list is in the
range F1:F10.

Enter this formula in L1 and copy down as needed:

=IF(COUNT(LOOKUP(2,1/SEARCH(F$1:F$10,D1))),J1,"")

--
Biff
Microsoft Excel MVP


Hi,

How to set a nested IF function to check column D for one or more
specific texts ,if matches it returns the corresponding value from
column J and go on the next row until it reaches the furnishing line
for example,
Column D Column J Column L

1) …travel… 200.00
IF(ISNUMBER(FIND("travel",$D1)),$J1,"") = 200.00
2) …air/fares 400.00
IF(ISNUMBER(FIND("travel",$D1)),$J1,"") = blank
3) …tolls… 50.00
IF(ISNUMBER(FIND("tolls",$D2)),$J2,"") = 50.00
4) …B’trips…travel… 124.00 Nested IF
formula ??? = 124.00
5) …B’trips…travel… tolls.. 75.00 Nested IF formula ??? =
75.00

For item 4), Nested IF formula for one or more specific texts ( ie
B'trip or travel )
For item 5), Nested IF formula for one or more specific texts ( ie
B'trip or travel or tolls )

Is it possible to set the above function such as like or contain to
check for one or more specific text from that row ?

Please help, many thanks

Regards
Len

 

[Len]

Try this...

Make a list of the words you want to look for. Assume this list is in the
range F1:F10.

Enter this formula in L1 and copy down as needed:

=IF(COUNT(LOOKUP(2,1/SEARCH(F$1:F$10,D1))),J1,"")

Hi Biff,

Thanks for reply and it works, this can also solve the limitation of
nested if function up to 7 levels

Regards
Len

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try this...

Make a list of the words you want to look for. Assume this list is in the
range F1:F10.

Enter this formula in L1 and copy down as needed:

=IF(COUNT(LOOKUP(2,1/SEARCH(F$1:F$10,D1))),J1,"")

Hi Biff,

Thanks for reply and it works, this can also solve the limitation of
nested if function up to 7 levels

Regards
Len

 

[Len]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP






Hi Biff,

Thanks for reply and it works, this can also solve the limitation of
nested if function up to 7 levels

Regards
Len











- Show quoted text -

Hi Biff,

From your code, I'm not quite understand the formula
=IF(COUNT(LOOKUP(2,1/SEARCH(F$1:F$10,D1))),J1,"") , appreciate if you
could explain "....(2,1/SEARCH(..... ", can it be any number and
division by 1/SEARCH ??

Thanks again

Regards
Len

 

[Manic]

If you are just looking to match, you can use the LOOKUP function on
Column D. For example
=Lookup(D1,{"travel","air/fares","tolls"},{200,400,50})
- Madhu

 

[Len]

If you are just looking to match, you can use the LOOKUP function on
Column D. For example
=Lookup(D1,{"travel","air/fares","tolls"},{200,400,50})
- Madhu








- Show quoted text -

Hi Madhu,

Thanks for your suggestion, it seems that your lookup function doen't
give the correct result , say place a formula to search " travel, air/
fares, tolls or more " from cell D1 and if match, it will return the
corresponding value from cell J1. Note that the search list may expand
due to case sensitive or abbreviation of text

Regards
Len