| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with VLOOKUP
Hello All Excel Guru's:
I have another quick question that I need assistance with that pertains or deals with conditional format. I have a list of names in one column and I need to search another list (range) to see if that name DOES NOT EXIST in that list or range. If it DOES NOT Exist, then to change the color of the cell. Listed below is the my example: A B C D E F 1 Names Semi 2 Hulk Hulk Storm Spidey Batman 3 Storm Storm Batman Ironman Spidey 4 Spidey IronMan Phoenix Batman Hulk 5 BatMan Spidey Batman Storm Hulk 6 SuperMan IronMan Hulk Spidey Phoenix 7 IronMan IronMan BatMan Hulk Storm 8 CatWm 9 Phoenix 10 Kitty A2:A10 is a range called Names C2:F7 is a range called SEMI In the above example, the following three (3) names should be colored out (CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the SEMI range. =vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think my formula is correct and also, do I put this in the conditional format so that I can color out the cell. I'm not very strong in using VLOOKUP, any and all help is greatly appreciated. Argus |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with VLOOKUP
How about:
formula is: =countif(semi,a1)=0 =vlookup() only looks for a match in the first column of the lookup range. OdAwG wrote: Hello All Excel Guru's: I have another quick question that I need assistance with that pertains or deals with conditional format. I have a list of names in one column and I need to search another list (range) to see if that name DOES NOT EXIST in that list or range. If it DOES NOT Exist, then to change the color of the cell. Listed below is the my example: A B C D E F 1 Names Semi 2 Hulk Hulk Storm Spidey Batman 3 Storm Storm Batman Ironman Spidey 4 Spidey IronMan Phoenix Batman Hulk 5 BatMan Spidey Batman Storm Hulk 6 SuperMan IronMan Hulk Spidey Phoenix 7 IronMan IronMan BatMan Hulk Storm 8 CatWm 9 Phoenix 10 Kitty A2:A10 is a range called Names C2:F7 is a range called SEMI In the above example, the following three (3) names should be colored out (CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the SEMI range. =vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think my formula is correct and also, do I put this in the conditional format so that I can color out the cell. I'm not very strong in using VLOOKUP, any and all help is greatly appreciated. Argus -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with VLOOKUP
That doesn't seem to work for all, when I copied your formula down the list,
it show FALSE for alot of names that appear in the SEMI range "Dave Peterson" wrote in message ... How about: formula is: =countif(semi,a1)=0 =vlookup() only looks for a match in the first column of the lookup range. OdAwG wrote: Hello All Excel Guru's: I have another quick question that I need assistance with that pertains or deals with conditional format. I have a list of names in one column and I need to search another list (range) to see if that name DOES NOT EXIST in that list or range. If it DOES NOT Exist, then to change the color of the cell. Listed below is the my example: A B C D E F 1 Names Semi 2 Hulk Hulk Storm Spidey Batman 3 Storm Storm Batman Ironman Spidey 4 Spidey IronMan Phoenix Batman Hulk 5 BatMan Spidey Batman Storm Hulk 6 SuperMan IronMan Hulk Spidey Phoenix 7 IronMan IronMan BatMan Hulk Storm 8 CatWm 9 Phoenix 10 Kitty A2:A10 is a range called Names C2:F7 is a range called SEMI In the above example, the following three (3) names should be colored out (CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the SEMI range. =vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think my formula is correct and also, do I put this in the conditional format so that I can color out the cell. I'm not very strong in using VLOOKUP, any and all help is greatly appreciated. Argus -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with VLOOKUP
Don't copy the formula down the list!
Select the range A2:A7. A2 should be the active cell. The active cell is clear while the rest of the cells in the selected range have a "blueish" color. Goto the menu FormatConditional Formatting Formula Is: =COUNTIF(semi,A2)=0 Click the Format button Select the style(s) desired OK out As long as the defined name "semi" has absolute references the CF formula should work. (unless there might be unseen characters like leading/trailing spaces in some cells) Biff "OdAwG" wrote in message ... That doesn't seem to work for all, when I copied your formula down the list, it show FALSE for alot of names that appear in the SEMI range "Dave Peterson" wrote in message ... How about: formula is: =countif(semi,a1)=0 =vlookup() only looks for a match in the first column of the lookup range. OdAwG wrote: Hello All Excel Guru's: I have another quick question that I need assistance with that pertains or deals with conditional format. I have a list of names in one column and I need to search another list (range) to see if that name DOES NOT EXIST in that list or range. If it DOES NOT Exist, then to change the color of the cell. Listed below is the my example: A B C D E F 1 Names Semi 2 Hulk Hulk Storm Spidey Batman 3 Storm Storm Batman Ironman Spidey 4 Spidey IronMan Phoenix Batman Hulk 5 BatMan Spidey Batman Storm Hulk 6 SuperMan IronMan Hulk Spidey Phoenix 7 IronMan IronMan BatMan Hulk Storm 8 CatWm 9 Phoenix 10 Kitty A2:A10 is a range called Names C2:F7 is a range called SEMI In the above example, the following three (3) names should be colored out (CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the SEMI range. =vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think my formula is correct and also, do I put this in the conditional format so that I can color out the cell. I'm not very strong in using VLOOKUP, any and all help is greatly appreciated. Argus -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with VLOOKUP
Hey Mr. "D"
I read it wrong, your formula does work correctly, for the names that it found it shows False, and the ones that are not in the list shows true. I had a brain fart there and I apologize. Thanks for the help... "Dave Peterson" wrote in message ... How about: formula is: =countif(semi,a1)=0 =vlookup() only looks for a match in the first column of the lookup range. OdAwG wrote: Hello All Excel Guru's: I have another quick question that I need assistance with that pertains or deals with conditional format. I have a list of names in one column and I need to search another list (range) to see if that name DOES NOT EXIST in that list or range. If it DOES NOT Exist, then to change the color of the cell. Listed below is the my example: A B C D E F 1 Names Semi 2 Hulk Hulk Storm Spidey Batman 3 Storm Storm Batman Ironman Spidey 4 Spidey IronMan Phoenix Batman Hulk 5 BatMan Spidey Batman Storm Hulk 6 SuperMan IronMan Hulk Spidey Phoenix 7 IronMan IronMan BatMan Hulk Storm 8 CatWm 9 Phoenix 10 Kitty A2:A10 is a range called Names C2:F7 is a range called SEMI In the above example, the following three (3) names should be colored out (CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the SEMI range. =vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think my formula is correct and also, do I put this in the conditional format so that I can color out the cell. I'm not very strong in using VLOOKUP, any and all help is greatly appreciated. Argus -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with VLOOKUP
Hey Sir,
Forgot to ask, how do I use this now with conditional format to change the cell color for those names that equal True Argus "Dave Peterson" wrote in message ... How about: formula is: =countif(semi,a1)=0 =vlookup() only looks for a match in the first column of the lookup range. OdAwG wrote: Hello All Excel Guru's: I have another quick question that I need assistance with that pertains or deals with conditional format. I have a list of names in one column and I need to search another list (range) to see if that name DOES NOT EXIST in that list or range. If it DOES NOT Exist, then to change the color of the cell. Listed below is the my example: A B C D E F 1 Names Semi 2 Hulk Hulk Storm Spidey Batman 3 Storm Storm Batman Ironman Spidey 4 Spidey IronMan Phoenix Batman Hulk 5 BatMan Spidey Batman Storm Hulk 6 SuperMan IronMan Hulk Spidey Phoenix 7 IronMan IronMan BatMan Hulk Storm 8 CatWm 9 Phoenix 10 Kitty A2:A10 is a range called Names C2:F7 is a range called SEMI In the above example, the following three (3) names should be colored out (CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the SEMI range. =vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think my formula is correct and also, do I put this in the conditional format so that I can color out the cell. I'm not very strong in using VLOOKUP, any and all help is greatly appreciated. Argus -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with VLOOKUP
Check the response from Biff (aka T. Valko). He gives instructions to you.
But... If you're going to do anything with this list, you may want to insert an adjacent cell and use that formula in that column. Then you can apply Data|Filter|autofilter and see the ones you want (either true or false). In fact, you may want to do both--use format|Conditional formatting and the helper column. OdAwG wrote: Hey Sir, Forgot to ask, how do I use this now with conditional format to change the cell color for those names that equal True Argus "Dave Peterson" wrote in message ... How about: formula is: =countif(semi,a1)=0 =vlookup() only looks for a match in the first column of the lookup range. OdAwG wrote: Hello All Excel Guru's: I have another quick question that I need assistance with that pertains or deals with conditional format. I have a list of names in one column and I need to search another list (range) to see if that name DOES NOT EXIST in that list or range. If it DOES NOT Exist, then to change the color of the cell. Listed below is the my example: A B C D E F 1 Names Semi 2 Hulk Hulk Storm Spidey Batman 3 Storm Storm Batman Ironman Spidey 4 Spidey IronMan Phoenix Batman Hulk 5 BatMan Spidey Batman Storm Hulk 6 SuperMan IronMan Hulk Spidey Phoenix 7 IronMan IronMan BatMan Hulk Storm 8 CatWm 9 Phoenix 10 Kitty A2:A10 is a range called Names C2:F7 is a range called SEMI In the above example, the following three (3) names should be colored out (CatWm, Superman, and Kitty) because those names DO NOT APPEAR in the SEMI range. =vlookup(A2,Semi,1,False) but I keep getting #N/A <-- I do not think my formula is correct and also, do I put this in the conditional format so that I can color out the cell. I'm not very strong in using VLOOKUP, any and all help is greatly appreciated. Argus -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
