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
|