| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with Excel 2000 text-related functions
How do I search a range of text cells and return value(s) based on my findings?
From C18, I want to search C3:C14, said cells sometimes containing names, to see if one particular name is there. If it is, I want to leave C18 blank. If it isn't, I want to insert the name in C18. I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!". Help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Excel 2000 text-related functions
Assuming A1 contains the name you are looking for put this formula in C18
=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"") Tyro "Joan A" <Joan wrote in message ... How do I search a range of text cells and return value(s) based on my findings? From C18, I want to search C3:C14, said cells sometimes containing names, to see if one particular name is there. If it is, I want to leave C18 blank. If it isn't, I want to insert the name in C18. I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!". Help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Excel 2000 text-related functions
Hi Joan,
Suppose you are looking for the name Shane in the range C3:C14. The formula in cell C18 would read: =IF(OR(C3:C14="Shane"),"Shane","") This is an array formula so when enter it you hold down Ctrl+Shift+Enter. To shorten this you can put the name you are searching for in another cell, say A1, then the formula becomes {=IF(OR(G2:G7=A1),A1,"")} The outer {}'s are not typed but you will see them in the cell when you press Ctrl+Shift+Enter. Cheers, Shane Devenshire "Joan A" <Joan wrote in message ... How do I search a range of text cells and return value(s) based on my findings? From C18, I want to search C3:C14, said cells sometimes containing names, to see if one particular name is there. If it is, I want to leave C18 blank. If it isn't, I want to insert the name in C18. I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!". Help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Excel 2000 text-related functions
Thanks, this worked!
But now, I'm wondering if I can add another IF in the same cell. I only want the first formula to run if the value in another cell equals something. For instance, if B16<OFF, then run =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,""). Or if it does equal OFF, then a blank cell overrides the formula you provided. Clear as mud? "Tyro" wrote: Assuming A1 contains the name you are looking for put this formula in C18 =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"") Tyro "Joan A" <Joan wrote in message ... How do I search a range of text cells and return value(s) based on my findings? From C18, I want to search C3:C14, said cells sometimes containing names, to see if one particular name is there. If it is, I want to leave C18 blank. If it isn't, I want to insert the name in C18. I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!". Help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Excel 2000 text-related functions
Thanks for your reply, but this didn't work. Blank when there should have
been something, error message when there shouldn't have been anything. "Shane Devenshire" wrote: Hi Joan, Suppose you are looking for the name Shane in the range C3:C14. The formula in cell C18 would read: =IF(OR(C3:C14="Shane"),"Shane","") This is an array formula so when enter it you hold down Ctrl+Shift+Enter. To shorten this you can put the name you are searching for in another cell, say A1, then the formula becomes {=IF(OR(G2:G7=A1),A1,"")} The outer {}'s are not typed but you will see them in the cell when you press Ctrl+Shift+Enter. Cheers, Shane Devenshire "Joan A" <Joan wrote in message ... How do I search a range of text cells and return value(s) based on my findings? From C18, I want to search C3:C14, said cells sometimes containing names, to see if one particular name is there. If it is, I want to leave C18 blank. If it isn't, I want to insert the name in C18. I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!". Help? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Excel 2000 text-related functions
=IF(B16<"OFF",IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1, ""),"")
Tyro "Joan A" wrote in message ... Thanks, this worked! But now, I'm wondering if I can add another IF in the same cell. I only want the first formula to run if the value in another cell equals something. For instance, if B16<OFF, then run =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,""). Or if it does equal OFF, then a blank cell overrides the formula you provided. Clear as mud? "Tyro" wrote: Assuming A1 contains the name you are looking for put this formula in C18 =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"") Tyro "Joan A" <Joan wrote in message ... How do I search a range of text cells and return value(s) based on my findings? From C18, I want to search C3:C14, said cells sometimes containing names, to see if one particular name is there. If it is, I want to leave C18 blank. If it isn't, I want to insert the name in C18. I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!". Help? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Excel 2000 text-related functions
=IF(B16="OFF","",IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A 1,""))
-- David Biddulph "Joan A" wrote in message ... Thanks, this worked! But now, I'm wondering if I can add another IF in the same cell. I only want the first formula to run if the value in another cell equals something. For instance, if B16<OFF, then run =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,""). Or if it does equal OFF, then a blank cell overrides the formula you provided. Clear as mud? "Tyro" wrote: Assuming A1 contains the name you are looking for put this formula in C18 =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"") Tyro "Joan A" <Joan wrote in message ... How do I search a range of text cells and return value(s) based on my findings? From C18, I want to search C3:C14, said cells sometimes containing names, to see if one particular name is there. If it is, I want to leave C18 blank. If it isn't, I want to insert the name in C18. I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!". Help? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with Excel 2000 text-related functions
Another one:
=IF(B16="OFF","",IF(COUNTIF(C3:C14,A1),"",A1)) -- Biff Microsoft Excel MVP "Joan A" wrote in message ... Thanks, this worked! But now, I'm wondering if I can add another IF in the same cell. I only want the first formula to run if the value in another cell equals something. For instance, if B16<OFF, then run =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,""). Or if it does equal OFF, then a blank cell overrides the formula you provided. Clear as mud? "Tyro" wrote: Assuming A1 contains the name you are looking for put this formula in C18 =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"") Tyro "Joan A" <Joan wrote in message ... How do I search a range of text cells and return value(s) based on my findings? From C18, I want to search C3:C14, said cells sometimes containing names, to see if one particular name is there. If it is, I want to leave C18 blank. If it isn't, I want to insert the name in C18. I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!". Help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2000, RAND and IF functions do not work together for me | Excel Worksheet Functions | |||
| Excel 2000 - Formulas and Functions | Excel Discussion (Misc queries) | |||
| EXCEL 2000 - COPY & PASTE FUNCTIONS! | Excel Discussion (Misc queries) | |||
| Extend Rows and related formula downwards using functions not macros | New Users to Excel | |||
| Excel 2000 Functions | Excel Worksheet Functions | |||
Linear Mode
