Home

Using IF statements to determine if any data in a large pool is present in a small pool

%3CLINGO-SUB%20id%3D%22lingo-sub-839851%22%20slang%3D%22en-US%22%3EUsing%20IF%20statements%20to%20determine%20if%20any%20data%20in%20a%20large%20pool%20is%20present%20in%20a%20small%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-839851%22%20slang%3D%22en-US%22%3E%3CP%3EAlright%2C%20so%20basically%20I%20have%20a%20big%20list%20of%20names%20each%20with%20their%20own%20unique%20number%20in%20the%20cell%20to%20the%20right.%20There%20are%20instances%20where%20the%20same%20name%20appears%20several%20times%20each%20with%20its%20own%20unique%20number.%20I%20have%20made%20a%20list%20of%20names%20that%20are%20no%20longer%20in%20my%20system.%20What%20I%20need%20is%20to%20find%20a%20function%20which%20will%20run%20each%20name%20in%20the%20large%20list%20against%20the%20small%20list%20and%20then%20return%20the%20unique%20number%20in%20the%20cell%20to%20the%20right%20of%20the%20name%20in%20the%20large%20list%20in%20a%20separate%20column.%20I%20have%20been%20unsuccessful%20with%20VLOOKUP%20and%20IF%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20513px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130384i1CA833C41A207923%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel%20IF%201.PNG%22%20title%3D%22excel%20IF%201.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESmall%20list%20on%20the%20left%2C%20Large%20list%20on%20the%20right%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20in%20this%20example%20my%20function%20%3DIF(G1%3DF2%3AF433%2CH2%2C%22NO%22)%20works%20only%20when%20the%20name%20is%20directly%20next%20to%20its%20instance%20in%20the%20small%20list.%20Even%20though%20there%20are%20multiple%20instances%20of%20%22AGULAR%22%20in%20the%20large%20list%20it%20fails%20to%20return%20the%20unique%20number.%20%22AHUJA%22%20is%20also%20in%20the%20small%20list%2C%20but%20the%20unique%20number%20associated%20with%20the%20name%20in%20the%20long%20list%20is%20not%20returned.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVLOOKUP%20seems%20like%20it%20would%20be%20a%20better%20option%2C%20however%20since%20one%20list%20is%20small%20than%20the%20other%20theres%20no%20way%20it%20can%20return%20the%20unique%20number%20when%20the%20reference%20area%20is%20a%20fraction%20of%20the%20size%20of%20the%20value%20area.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20provide%20more%20details%20if%20needed%2C%20this%20has%20been%20dominating%20my%20day%20and%20I'm%20losing%20the%20ability%20to%20explain%20the%20issues%20I'm%20having.%20If%20anyone%20else%20has%20found%20a%20solution%20to%20a%20problem%20like%20this%20I%20would%20appreciate%20it%20so%20much%20if%20you%20would%20share%20it%20with%20me%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3CP%3ETim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-839851%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840189%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20statements%20to%20determine%20if%20any%20data%20in%20a%20large%20pool%20is%20present%20in%20a%20small%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840189%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404370%22%20target%3D%22_blank%22%3E%40tfdavid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBased%20on%20the%20screenshot%2C%20please%20try%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20I2%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(ISNA(MATCH(G2%2CF%3AF%2C0))%2C%22NO%22%2CH2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20doesn't%20work%20as%20desired%2C%20please%20upload%20a%20sample%20file%20along%20with%20your%20desired%20output%20mocked%20up%20manually%20in%20a%20column%20for%20few%20rows%20to%20illustrate%20what%20you%20are%20trying%20to%20achieve.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840938%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20statements%20to%20determine%20if%20any%20data%20in%20a%20large%20pool%20is%20present%20in%20a%20small%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840938%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThis%20worked%20perfectly%2C%20thank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841059%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20statements%20to%20determine%20if%20any%20data%20in%20a%20large%20pool%20is%20present%20in%20a%20small%20pool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841059%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404370%22%20target%3D%22_blank%22%3E%40tfdavid%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20the%20solution%20offered%20worked%20for%20you%20as%20desired%2C%20please%20take%20a%20minute%20to%20accept%20the%20solution%20as%20an%20Accepted%20Answer%20and%20hit%20the%20Like%20button.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
tfdavid
New Contributor

Alright, so basically I have a big list of names each with their own unique number in the cell to the right. There are instances where the same name appears several times each with its own unique number. I have made a list of names that are no longer in my system. What I need is to find a function which will run each name in the large list against the small list and then return the unique number in the cell to the right of the name in the large list in a separate column. I have been unsuccessful with VLOOKUP and IF

 

excel IF 1.PNG

Small list on the left, Large list on the right

As you can see in this example my function =IF(G1=F2:F433,H2,"NO") works only when the name is directly next to its instance in the small list. Even though there are multiple instances of "AGULAR" in the large list it fails to return the unique number. "AHUJA" is also in the small list, but the unique number associated with the name in the long list is not returned.

 

VLOOKUP seems like it would be a better option, however since one list is small than the other theres no way it can return the unique number when the reference area is a fraction of the size of the value area. 

 

I can provide more details if needed, this has been dominating my day and I'm losing the ability to explain the issues I'm having. If anyone else has found a solution to a problem like this I would appreciate it so much if you would share it with me

 

Cheers!

Tim

3 Replies

@tfdavid 

Based on the screenshot, please try this...

 

In I2

=IF(ISNA(MATCH(G2,F:F,0)),"NO",H2)

and copy it down.

 

If that doesn't work as desired, please upload a sample file along with your desired output mocked up manually in a column for few rows to illustrate what you are trying to achieve.

You're welcome@tfdavid! Glad it worked as desired.

 

Since the solution offered worked for you as desired, please take a minute to accept the solution as an Accepted Answer and hit the Like button.

 

Related Conversations