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

Copper 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