Cross indexing 2 lists that have identical content but in different order (Trying to get numbers to then match with the lists)

[McCW]

I am trying to have two lists in real life that are able to be printed out and used to cross-index. What I mean by that is I am trying to have two lists:

A) One that is ALPHABETICAL and
B) One that is CATEGORIZED

but both have the same content, just arranged differently.

What I want to be able to do is to get numbers that correspond with each respective cell for the CATEGORIZED list to then match up with the ALPHABETICAL list.

For example:

CATEGORIZED: ALPHABETICAL:

1. THIS IS AN EXAMPLE 2. APPLE
2. APPLE 5. GRAPE
3. ORANGE 6. LEMON
4. PEAR 3. ORANGE
5. GRAPE 4. PEAR
6. LEMON 1. THIS IS AN EXAMPLE



Basically I want the cells in the Alphabetical to read the list in Categorized, find matching things (So like Apple = Apple) then take the NUMBER in the cell next to it in the CATEGORIZED list and put that number next to the matching thing on the Alphabetical list.

Confusing, but I think pretty simple if you can track with me.

Again it's 550 entries, so it would take a long time to do this manually and they change sometimes as well, so this is something I want to be able to do over and over if possible.

 

[McCW]

CATEGORIZED:

1. THIS IS AN EXAMPLE
2. APPLE 5. GRAPE
3. ORANGE 6. LEMON
4. PEAR 3. ORANGE
5. GRAPE 4. PEAR
6. LEMON 1. THIS IS AN EXAMPLE


ALPHABETICAL:

 

[McCW]

Sorry. Formatting issues.

CATEGORIZED:

1. THIS IS AN EXAMPLE
2. APPLE 5. GRAPE
3. ORANGE 6. LEMON
4. PEAR 3. ORANGE
5. GRAPE 4. PEAR
6. LEMON 1. THIS IS AN EXAMPLE


ALPHABETICAL:
2. APPLE
5. GRAPE
6. LEMON
3. ORANGE
4. PEAR
1. THIS IS AN EXAMPLE

 

[McCW]

Wow, I really wish there was an "edit" function I could find. Please just consider that the "categorized" list example above only had the first set of numbers, IE 1-6 and the 5,6,3,4,1 next to them were removed.

 

[AhoyNC]

Like this?

Book2
	A	B	C	D	E
1		CATEGORIZED			ALPHABETICAL
2	1.	THIS IS AN EXAMPLE		2.	APPLE
3	2.	APPLE		5.	GRAPE
4	3.	ORANGE		6.	LEMON
5	4.	PEAR		3.	ORANGE
6	5.	GRAPE		4.	PEAR
7	6.	LEMON		1.	THIS IS AN EXAMPLE
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=INDEX($A$2:$A$7,MATCH(E2,$B$2:$B$7,0))

 

[McCW]

Yup, you got it 100%. Thanks very much.

 

[AhoyNC]

You're welcome. Thanks for the feedback.