| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Arrays - Nested IF with Vlookup?
Hi Frank,
I am trying to match data in two separate columns, but the names are not identical in each column (e.g., ABC Toys vs ABC Toys, Inc.). If I want to run a vlookup that identifies whether any part of cell A is contained in range B. Ex. Since "ABC Toys, Inc." contains "ABC Toys", I would like to have a "True" identifier or something similar. Any idea on how to do this? Yours, V. Lookup Challenged "Frank Kabel" wrote: Hi then check the names. Do they really match (check for spaces, etc.) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... That worked where I was getting the #NA error message but it blanked out the contents of the cells that showed valid names...? "Frank Kabel" wrote: Hi sure you can :-) Try: =IF(ISNA(MATCH("Moderator",$D$2:$D$27,0)),"",INDEX ($B$2:$B$27,MATCH("Mo derator",$D$2:$D$27,0))) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... Thanks Frank. That worked great. I added $ to make the cell ranges absolute and copied the function to other cells - Modifying the "Moderator" to the other text options. Can I add something to the line so that if there is no "moderator", for example, the cell shows a blank space rather than an error "#N/A"? Many thanks for your help. Jo "Frank Kabel" wrote: Hi try =INDEX(B2:B27,MATCH("Moderator",D2:D27,0)) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... How do I use an if statement and have the result show a specific cell, located in another worksheet, using the vlookup? E.g. IF cell range D2:D27 contains the text "Moderator" then show the first name (located, for example, in rage B2:B27). |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Arrays - Nested IF with Vlookup?
Try this:
A1 = ABC Toys =ISNUMBER(MATCH("*"&A1&"*",B1:B10,0)) -- Biff Microsoft Excel MVP "RV" wrote in message ... Hi Frank, I am trying to match data in two separate columns, but the names are not identical in each column (e.g., ABC Toys vs ABC Toys, Inc.). If I want to run a vlookup that identifies whether any part of cell A is contained in range B. Ex. Since "ABC Toys, Inc." contains "ABC Toys", I would like to have a "True" identifier or something similar. Any idea on how to do this? Yours, V. Lookup Challenged "Frank Kabel" wrote: Hi then check the names. Do they really match (check for spaces, etc.) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... That worked where I was getting the #NA error message but it blanked out the contents of the cells that showed valid names...? "Frank Kabel" wrote: Hi sure you can :-) Try: =IF(ISNA(MATCH("Moderator",$D$2:$D$27,0)),"",INDEX ($B$2:$B$27,MATCH("Mo derator",$D$2:$D$27,0))) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... Thanks Frank. That worked great. I added $ to make the cell ranges absolute and copied the function to other cells - Modifying the "Moderator" to the other text options. Can I add something to the line so that if there is no "moderator", for example, the cell shows a blank space rather than an error "#N/A"? Many thanks for your help. Jo "Frank Kabel" wrote: Hi try =INDEX(B2:B27,MATCH("Moderator",D2:D27,0)) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... How do I use an if statement and have the result show a specific cell, located in another worksheet, using the vlookup? E.g. IF cell range D2:D27 contains the text "Moderator" then show the first name (located, for example, in rage B2:B27). |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Arrays - Nested IF with Vlookup?
Biff,
Thanks for your help. That worked like a charm! RV "T. Valko" wrote: Try this: A1 = ABC Toys =ISNUMBER(MATCH("*"&A1&"*",B1:B10,0)) -- Biff Microsoft Excel MVP "RV" wrote in message ... Hi Frank, I am trying to match data in two separate columns, but the names are not identical in each column (e.g., ABC Toys vs ABC Toys, Inc.). If I want to run a vlookup that identifies whether any part of cell A is contained in range B. Ex. Since "ABC Toys, Inc." contains "ABC Toys", I would like to have a "True" identifier or something similar. Any idea on how to do this? Yours, V. Lookup Challenged "Frank Kabel" wrote: Hi then check the names. Do they really match (check for spaces, etc.) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... That worked where I was getting the #NA error message but it blanked out the contents of the cells that showed valid names...? "Frank Kabel" wrote: Hi sure you can :-) Try: =IF(ISNA(MATCH("Moderator",$D$2:$D$27,0)),"",INDEX ($B$2:$B$27,MATCH("Mo derator",$D$2:$D$27,0))) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... Thanks Frank. That worked great. I added $ to make the cell ranges absolute and copied the function to other cells - Modifying the "Moderator" to the other text options. Can I add something to the line so that if there is no "moderator", for example, the cell shows a blank space rather than an error "#N/A"? Many thanks for your help. Jo "Frank Kabel" wrote: Hi try =INDEX(B2:B27,MATCH("Moderator",D2:D27,0)) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... How do I use an if statement and have the result show a specific cell, located in another worksheet, using the vlookup? E.g. IF cell range D2:D27 contains the text "Moderator" then show the first name (located, for example, in rage B2:B27). |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Arrays - Nested IF with Vlookup?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "RV" wrote in message ... Biff, Thanks for your help. That worked like a charm! RV "T. Valko" wrote: Try this: A1 = ABC Toys =ISNUMBER(MATCH("*"&A1&"*",B1:B10,0)) -- Biff Microsoft Excel MVP "RV" wrote in message ... Hi Frank, I am trying to match data in two separate columns, but the names are not identical in each column (e.g., ABC Toys vs ABC Toys, Inc.). If I want to run a vlookup that identifies whether any part of cell A is contained in range B. Ex. Since "ABC Toys, Inc." contains "ABC Toys", I would like to have a "True" identifier or something similar. Any idea on how to do this? Yours, V. Lookup Challenged "Frank Kabel" wrote: Hi then check the names. Do they really match (check for spaces, etc.) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... That worked where I was getting the #NA error message but it blanked out the contents of the cells that showed valid names...? "Frank Kabel" wrote: Hi sure you can :-) Try: =IF(ISNA(MATCH("Moderator",$D$2:$D$27,0)),"",INDEX ($B$2:$B$27,MATCH("Mo derator",$D$2:$D$27,0))) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... Thanks Frank. That worked great. I added $ to make the cell ranges absolute and copied the function to other cells - Modifying the "Moderator" to the other text options. Can I add something to the line so that if there is no "moderator", for example, the cell shows a blank space rather than an error "#N/A"? Many thanks for your help. Jo "Frank Kabel" wrote: Hi try =INDEX(B2:B27,MATCH("Moderator",D2:D27,0)) -- Regards Frank Kabel Frankfurt, Germany "J" schrieb im Newsbeitrag ... How do I use an if statement and have the result show a specific cell, located in another worksheet, using the vlookup? E.g. IF cell range D2:D27 contains the text "Moderator" then show the first name (located, for example, in rage B2:B27). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP NESTED | Excel Worksheet Functions | |||
| Nested IF with a VLOOKUP? | Excel Worksheet Functions | |||
| Multiple Arrays, Vlookup | Excel Worksheet Functions | |||
| Using Vlookup in formula arrays | Excel Worksheet Functions | |||
| Employing constant arrays to limit nested IF statements. | Excel Worksheet Functions | |||
Linear Mode
