Vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-3072829%22%20slang%3D%22en-US%22%3EVlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3072829%22%20slang%3D%22en-US%22%3E%3CP%3EI%20use%20Excel%20version%202112.%20I%20have%20a%20list%20of%20College%20names%20that%20I%20search%20for%20to%20return%20a%20value%20that%20%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22xl2.png%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342613i5D839BCF53A0F72C%2Fimage-size%2Fsmall%3Fv%3Dv2%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22xl2.png%22%20alt%3D%22xl2.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22xl1.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342615iCD644ED4C67A67A6%2Fimage-size%2Fsmall%3Fv%3Dv2%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22xl1.jpg%22%20alt%3D%22xl1.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22XL.jpg%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342614iB05E0DD707C8E394%2Fimage-size%2Fsmall%3Fv%3Dv2%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22XL.jpg%22%20alt%3D%22XL.jpg%22%20%2F%3E%3C%2FSPAN%3EI%20have%20assigned.%20However%20when%20I%20use%20vlookup%20it%20returns%20a%20singular%20name%20from%20the%20column%20and%20cannot%20return%20variations%20of%20the%20name%20such%20as%20I%20will%20get%20Colorada%20%2C%20but%20not%20Colorada%20ST.%20also%20if%20the%20college%20has%20a%20prefix%20such%20as%20N.%20Carolina%20the%20lookup%20will%20not%20return%20a%20value-and-%20so%20forth%20on%20all%20other%20colleges%20with%20similar%20names.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%26nbsp%3B%3DVLOOKUP(B7%2CDEVIATION!A%3AD%2C3%2CFALSE)%20and%20as%20stated%20it%20pulls%20all%20correct%20data%20but%20only%20those%20with%20a%20singular%20name.%20I%20am%20almost%2080%20and%20have%20no%20experience%20with%20excel.%2C%20but%20attempting%20to%20learn.%20Thanks%20for%20your%20help.%20Email%20address%20removed%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3072829%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3073533%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073533%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1287726%22%20target%3D%22_blank%22%3E%40steve560%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20only%20commend%20you%20that%20you%20want%20to%20learn%20new%20things!%20Since%20you%20mention%20to%20be%20using%20Office365%2C%20your%20Excel%20version%20should%20support%20the%20new%20dynamic%20array%20functions.%20On%20of%20these%20is%20FILTER.%20Combined%20with%20ISNUMBER%20and%20SEARC%2C%20you%20can%20extract%20all%20information%20from%20a%20structured%20table%2C%20based%20on%20a%20partial%20search%20phrase.%20Attached%20a%20file%20demonstrating%20it.%20See%20if%20you%20can%20get%20started%20with%20this%20on%20your%20own.%20If%20not%2C%20come%20back%20here.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_0-1643268164768.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342876iBF71EF7E4D5E1FD4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_0-1643268164768.png%22%20alt%3D%22Riny_van_Eekelen_0-1643268164768.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3075604%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3075604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1287726%22%20target%3D%22_blank%22%3E%40steve560%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERiny%2C%3C%2FP%3E%3CP%3EI%20don't%20have%20words%20to%20express%20my%20Thanks%20to%20you%20for%20your%20time%20and%20assistance.%20Many%20people%20these%20days%20don't%20help.%20I%20am%20very%20proud%20to%20make%20your%20acquaintance.%20However%2C%20I%20will%20waste%20no%20more%20of%20your%20time%20with%20this.%20My%20filter%20icon%20does%20not%20offer%20the%20same%20choices%20as%20yours.%20Even%20though%20I%20highlighted%20many%20areas%20for%20filtering%20I%20got%20the%20same%20-no%20results-no%20choices.%20I%20then%20attempted%20to%20input%20your%20formula%20changing%20to%20my%20cells%2C%20but%20again%20I%20failed%20to%20achieve%20results.%20To%20complicate%20matters%20I%20have%20two%20columns%20of%20data%20to%20extract%20when%20the%20college%20is%20chosen%20thus%20creating%20a%20false%20in%20the%20formula%20depending%20on%20which%20column%20I%20am%20asking%20for.%20To%20understand%20I%20seek%20standard%20deviation%20that%20I%20have%20already%20computed%20depending%20on%20whether%20the%20team%20is%20home%20or%20away%20when%20playing%20basketball.%20I%20am%20stumped%2C%20but%20will%20live%20with%20it%20for%20now.%20Keep%20helping%20others%2C%20it%20comes%20back%20with%20large%20rewards!%20Steve%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I use Excel version 2112. I have a list of College names that I search for to return a value that xl2.pngxl1.jpgXL.jpgI have assigned. However when I use vlookup it returns a singular name from the column and cannot return variations of the name such as I will get Colorado , but not Colorado ST. also if the college has a prefix such as N. Carolina the lookup will not return a value-and- so forth on all other colleges with similar names. 

I use =VLOOKUP(B7,DEVIATION!A:D,3,FALSE) and as stated it pulls all correct data but only those with a singular name. I am almost 80 and have no experience with excel., but attempting to learn. Thanks for your help. Email address removed

 

5 Replies

@steve560 

Can only commend you that you want to learn new things! Since you mention to be using Office365, your Excel version should support the new dynamic array functions. On of these is FILTER. Combined with ISNUMBER and SEARC, you can extract all information from a structured table, based on a partial search phrase. Attached a file demonstrating it. See if you can get started with this on your own. If not, come back here.

Riny_van_Eekelen_0-1643268164768.png

 

 

@steve560 

 

Riny,

I don't have words to express my Thanks to you for your time and assistance. Many people these days don't help. I am very proud to make your acquaintance. However, I will waste no more of your time with this. My filter icon does not offer the same choices as yours. Even though I highlighted many areas for filtering I got the same -no results-no choices. I then attempted to input your formula changing to my cells, but again I failed to achieve results. To complicate matters I have two columns of data to extract when the college is chosen thus creating a false in the formula depending on which column I am asking for. To understand I seek standard deviation that I have already computed depending on whether the team is home or away when playing basketball. I am stumped, but will live with it for now. Keep helping others, it comes back with large rewards! Steve

@steve560 Always glad to help, and I will not give up yet.

You wrote "My filter icon does not offer the same choices as yours." Well, that's because the FILTER I'm talking about is a function, i.e. not related to a button or an icon.

 

When you look in cell G5, you'll see it contains this formula:

=FILTER(Table1,ISNUMBER(SEARCH(F5,Table1[College])))

 It filters out all rows from the table (Table1) where a search for the keyword (in F5) in the College column of Table1 results in a number. Looking into each of the functions used (the ones in blue above) separately you'll get this:

 

Screenshot 2022-01-28 at 06.55.51.png

The SEARCH function returns an array with value errors (keyword not found) and numbers (keyword found). 

 

Screenshot 2022-01-28 at 06.57.18.png

ISNUMBER returns an array of Trues and False, depending on what's in the outcome of SEARCH (in this example referred to as K5# because that where the SEARCH array is located and the # means it picks up the entire array).

Now, the FILTER function picks-up all the rows in Table1, where it finds TRUE in the ISNUMBER step (i.e. the 4th and the 5th row.

 

When you study the FILTER function further you will note that you can use more than one filter criteria. 

For instance, you could use:

=FILTER(Table1,(ISNUMBER(SEARCH(F5,Table1[College])))*(Table1[Value]>4))

Put each criteria in it's own set of brackets and use * to apply a logical AND or + to apply a logical OR. This example will only return the 5th row as it is the only one with "colo" in the College column AND a value greater than 4 in the Value column.

I worked at this most of the weekend. It works!! Not sure what I did, but now the original vlookup works perfectly. It came about while was trying those different functions. So had you not encouraged me it would had remained a failure. Wish I could explain what happened, but I can't. Thank you so much for the interest in helping and getting me to success!!

@steve560 Glad you worked it out!