Nest IF function
 

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

Nest IF function
 

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


"Len" wrote in message
...
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

Nest IF function
 

On Jun 16, 12:18*am, "T. Valko" wrote:
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 Biff,

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

Regards
Len



"Len" wrote in message

...
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

Nest IF function
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Len" wrote in message
...
On Jun 16, 12:18 am, "T. Valko" wrote:
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 Biff,

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

Regards
Len



"Len" wrote in message

...
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

Nest IF function
 

On Jun 17, 12:00*am, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Len" wrote in message

...
On Jun 16, 12:18 am, "T. Valko" wrote:

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 Biff,

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

Regards
Len





"Len" wrote in message

....
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- Hide quoted text -

- 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


--

Nest IF function
 

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
On Jun 15, 7:13*pm, Len wrote:
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

Nest IF function
 

On Jun 18, 2:56*pm, Manic wrote:
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
On Jun 15, 7:13*pm, Len wrote:



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- Hide quoted text -

- 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