Forum Discussion
Excel vlookup help
Hi, so in column a i have a list of multiple different items. These range from stuff like "payroll23214.."etc. to insurance23423 for example. In cell b2 its blank. I need to fill the column with a short description of what is contained in column a. For example, for "payroll23214" , the cell in column b would be "payroll". So i tried creating a vlookup with that identified a partial text of column a so it sees the term payroll and that populates all of column b that are next to it that have the term payroll associated with it. I can't seem to get this working, any help would be appreciated.
=INDEX($E$4:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($D$4:$D$7,A2)),0))You can try this formula along with a reference table. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
4 Replies
=LEFT(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&"0123456789"))-1)Does this return the intended result?
- Thanks for the reply, ill be more specific.
The items in column a may not look like that all the time, the payroll term could be in the middle of numbers or even some term may not directly look like they are meant to match. For example something in column a described as "123qwe456" i would have a lookup function that would see the letters qwe and then describe it as "rty" in column b.=INDEX($E$4:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($D$4:$D$7,A2)),0))You can try this formula along with a reference table. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.