| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 -- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
| nest functions in CELL function | Excel Worksheet Functions | |||
| HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
| Is it possible to nest more than 7 arguments in one function? | Excel Discussion (Misc queries) | |||
| HOW DO I NEST MORE THAN 1 IF FUNCTION? | Excel Worksheet Functions | |||
Linear Mode
