Combine Index with indirect function

[Tempt]

Hi everyone,

I am stuck trying to figure out some formula in Excel. I have a column of some descriptions of a transaction and I need to aasign to them names (for example, to whom they belong or just for the purpose of making sense out of them). (Table 1)
By looking at them, I found some patterns, namely words or expressions they start with. I then generated a separate table with those beginnings of cells with a separate column of names. Furthermore, I generated a column of the length of the cells that contain those beginnings.
Now I am trying to figure out a formula that would look at the left of description cells (number of characters to be looked up to the left correspond to the column of lengths) and then when they match any of the cells of the first column of Table 2, then return the name that is in the second column of the table.
For demonstration, the table look like this:

Table 1
Description
.
.
.

Table 2
Beginnings | Names | Lengths
. . .
. . .
. . .

I need a formula for the second column of Table 1 that returns some content from the second column of table 2.
The formula for B2 in Table 1 looks like this:

=INDEX(Table2!A2:C190,MATCH(LEFT(Table1!A2;INDIRECT("Lengths")),INDIRECT("Beginnings"),0),2)

The formula doesn't work properly somehow returning the needed for some cells and not returning for others.

It would be cool if someone could tell me what I am doing wong or suggest any other possible solutions.

Thanks in advance!

 

[FormR]

Hi, welcome to the forum!

If I've understood correctly, then I don't think we need the lengths column and here is an option you can try:

Excel 2013/2016
	A	B	C	D	E
1	Description	Name		Beginnings	Names
2	hello world	name 1		hello	name 1
3	mr Excel forum	Name 2		mr Excel	Name 2
4	more text	Name 4		another	Name 3
5	text more	Name 5		more	Name 4
6				text	Name 5
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(2,1/COUNTIFS(A2,$D$2:$D$6&"*"),$E$2:$E$6)

If this doesn't help, maybe re-post you example tables but fill in those ". . ." with some actual examples.

 

[Tempt]

Hey, MrExcel MVP!

Thanks for the answer. It worked perfectly. This is exactly what I needed. You are my savior.

Would also be cool if you also could explain what the formula exactly does and how it works.

Cheers

 

[FormR]

Great, good to hear.

Try using the "evaluate formula" option on the "formulas" tab to evaluate each step of the calculation.

Keep in mind we are taking advantage of the fact that LOOKUP() ignores errors and can process arrays without the need for control+shift+enter.