Associating specific text in a cell to return values from a table

All right,

So my problem is thus:

I am exporting data from a inventory control system. This system gives me a material description. Our work area has 3 different zones that the material is run in.

Here is what I need. I need to be able to paste my information in and based off the material description I want a formula to look in a 2 column table and match up the description with the zone number.

I have attached the sheet.

hi reddragonbc,

Welcome to the forum. Edit BTW many of us cannot read pics and screenshots. You usually get faster / better solutions if you upload an Excel spreadsheet file ... desensitized of course. I improvised below from your description.

See if this is something you can work with.

Put your Description in E1

In column C a helper column ... formula in C2 and filled down.

Formula:

=IF(B2=$E$1,ROWS($1:1),"")

In F2 and filled down until you get blanks ...

Formula:

=IFERROR(INDEX($A$2:$A$21,SMALL($C$2:$C$21,ROWS($1:1))),"")

Row\Col	A	B	C	D	E	F
1	Zones	Descript			Description 14	Zone2
2	Zone3	Description 3				Zone2
3	Zone2	Description 3
4	Zone1	Description 1
5	Zone2	Description 2
6	Zone1	Description 2
7	Zone2	Description 14	6
8	Zone1	Description 8
9	Zone1	Description 9
10	Zone2	Description 12
11	Zone2	Description 6
12	Zone3	Description 6
13	Zone1	Description 2
14	Zone2	Description 11
15	Zone2	Description 8
16	Zone2	Description 8
17	Zone2	Description 12
18	Zone3	Description 13
19	Zone2	Description 4
20	Zone2	Description 14	19
21	Zone2	Description 3

Try this in D2 and filled down.

Formula:

=INDEX($B$2:$B$13,MATCH(E2,$A$2:$A$13,0))

Originally Posted by FlameRetired

=INDEX($B$2:$B$13,MATCH(E2,$A$2:$A$13,0))

Another way...

=VLOOKUP(E2,$A$2:$B$13,2,0)

Double posted.

@ Tony Valko,

Yup! Fewer function calls.

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.