# compare a number to three lists in excel Excel Excel 2007
November 19, 2010 at 07:31:02
Specs: Windows Vista
 i'm trying to compare a number to three lists and mark which list it is in.examples of what i have tried:{=IF(G66=ELSN, "ELS", IF(G66=LIN, "LI", IF(G66=TFN, "TF", "NO DATA")))}{=IF(EXACT(M81,LIN),"LI", IF(EXACT(M81,ELSN),"ELS", IF( EXACT(M81,TFN),"TF", "FAIL")))}{=IF(G2=NUM!B1:B84, "ELS", IF(G2=NUM!F1:F301, "LI", IF(G2=NUM!J1:J29, "TF", "NO DATA")))}these only work if the item is in the first posisition of the list.i have tried using the range insted of the named range so list!A1:A50is it possible to do what im trying to do? See More: compare a number to three lists in excel

#1 November 19, 2010 at 10:25:08
 re: "mark which list it is in"I'm not sure what you mean by "mark" which list it's in. Do you mean that you want to return the name of the Named Range it is found in?If so, perhaps this would work, assuming you have three named ranges - List1, List2, List3 - and the value you are looking for is in A1.=IF(NOT(ISNA(VLOOKUP(A1,List1,1,0))),"List1",IF(NOT(ISNA(VLOOKUP(A1,List2,1,0))),"List2",IF(NOT(ISNA(VLOOKUP(A1,List3,1,0))),"List3","Value Not Found")))VLOOKUP return #N/A if a value is not found in its lookup_array. If the value is found in the list, the NOT(ISNA) will be TRUE and the text associated with that IF statement will be returned.Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

#2
November 19, 2010 at 11:47:04
 exactly. i want to display what list it what found in, (a list, b list, c list). i will try what you have posted and get back to you.thanks for the help!EDIT:Totally works. thank you so much!

Report •
Related Solutions 