Home

VLOOKUP, INDEX or MATCH or a combination or some other formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-879425%22%20slang%3D%22en-US%22%3EVLOOKUP%2C%20INDEX%20or%20MATCH%20or%20a%20combination%20or%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879425%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20attached%20file%20you%20will%20see%20on%20Sheet%20%22Code%20Letter%20Info.%22%20the%20information%20in%20column%20B%20is%20entered%20manually%20and%20I%20have%20placed%20formulas%20in%20columns%20F%20thru%20M.%20However%20the%20formulas%20are%20extracting%20information%20from%20%22Parcel%20Info.%20%22Sheet%20from%20various%20columns%2C%20not%20the%20cells%20I%20am%20referring%20to%20in%20the%20formula.%20I%20tried%20a%20different%20formula%20in%20H2%20and%20H3%20and%20H4%20but%20those%20don't%20seem%20to%20work%20either.%20Can%20someone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-879425%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-879528%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%2C%20INDEX%20or%20MATCH%20or%20a%20combination%20or%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416408%22%20target%3D%22_blank%22%3E%40pharrison%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20ID%3As%20in%20Parcel%20Info%20have%20lot%20of%20trailing%20spaces%2C%20thus%20there%20is%20no%20match.%20Not%20sure%20from%20which%20column%20you'd%20like%20to%20pick-up%20the%20information%2C%20if%2C%20for%20example%2C%20from%20S%2C%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX('Parcel%20Info.'!S1%3AS5%2CMATCH(B2%2CTRIM('Parcel%20Info.'!B1%3AB5)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESee%20H2%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879751%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%2C%20INDEX%20or%20MATCH%20or%20a%20combination%20or%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20Do%20you%20know%20how%20I%20can%20delete%20the%20spaces%20without%20going%20into%20each%20cell%20individually%3F%20I%20tried%20%3Dtrim(B%3AB)%20but%20it%20did%20not%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879805%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%2C%20INDEX%20or%20MATCH%20or%20a%20combination%20or%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416408%22%20target%3D%22_blank%22%3E%40pharrison%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20replace%20the%20leading%20or%20trailing%20spaces%20in%20column%20B%2C%20follow%20these%20steps...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ESelect%20whole%20column%20B.%3C%2FLI%3E%3CLI%3EPress%20Ctrl%2BH%20to%20open%20Replace%20Window.%3C%2FLI%3E%3CLI%3EClick%20inside%20the%20Find%20what%3A%20box%20and%20press%20space%20bar%20once%20to%20type%20a%20space%20character.%3C%2FLI%3E%3CLI%3ELeave%20the%20Replace%20with%3A%20box%20empty.%3C%2FLI%3E%3CLI%3EClick%20on%20Replace%20All.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EThis%20method%20will%20remove%20all%20the%20spaces%20from%20Column%20B.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879863%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%2C%20INDEX%20or%20MATCH%20or%20a%20combination%20or%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879863%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20worked!%26nbsp%3BThank%20you%20guys%20very%20much!%3C%2FP%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%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879877%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%2C%20INDEX%20or%20MATCH%20or%20a%20combination%20or%20some%20other%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879877%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416408%22%20target%3D%22_blank%22%3E%40pharrison%3C%2FA%3E!%3C%2FP%3E%3C%2FLINGO-BODY%3E
pharrison
New Contributor

In the attached file you will see on Sheet "Code Letter Info." the information in column B is entered manually and I have placed formulas in columns F thru M. However the formulas are extracting information from "Parcel Info. "Sheet from various columns, not the cells I am referring to in the formula. I tried a different formula in H2 and H3 and H4 but those don't seem to work either. Can someone help?

5 Replies

@pharrison 

You ID:s in Parcel Info have lot of trailing spaces, thus there is no match. Not sure from which column you'd like to pick-up the information, if, for example, from S, formula could be

=INDEX('Parcel Info.'!S1:S5,MATCH(B2,TRIM('Parcel Info.'!B1:B5),0))

See H2 in attached.

@Sergei Baklan 

  Do you know how I can delete the spaces without going into each cell individually? I tried =trim(B:B) but it did not work.

@pharrison 

To replace the leading or trailing spaces in column B, follow these steps...

 

  1. Select whole column B.
  2. Press Ctrl+H to open Replace Window.
  3. Click inside the Find what: box and press space bar once to type a space character.
  4. Leave the Replace with: box empty.
  5. Click on Replace All.

This method will remove all the spaces from Column B.

That worked! Thank you guys very much!

@Subodh_Tiwari_sktneer 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies