Excel Excel 2007

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:A50

is it possible to do what im trying to do?

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 valueisfound 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 thisHow-To.

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!

Ask Your Question

Weekly Poll