Assign formula

[mantasm1]

Hello guys,

Cant solve this function, so decided to write here. Maybe you can help me.

Situation:

Have a huge list (like a 30k+ rows). in A Column i have Numbers and in B i have Names. Basically i get a random list of names and i need to assign a exact number from list.

Example:

My database/list consists of:

A	B
101	Apple
102	Tomato
103	Lemon
104	Orange
...	...
208	Potato
209	Onion
210	Cucumber
...	...
etc	etc

<tbody>
</tbody>

and i got random list of NAMES and need assign right NUMBER (for ex. i paste that list to column C and want to get a right number to D)

C	D (need that formula)
Potato	208
Cucumber	210
Apple	101
Orange	104
Tomato	102
and etc

<tbody>
</tbody>

I hope u get what i mean..

So anybody? I would be grateful for this.

 

[Chandresh]

HI you can use index match formula

 

[Chandresh]

=index(a:a,match(c4,b:b,0))

 

[mantasm1]

Hey man, thank you. That was enought, it worked.

here is solution:

=INDEX($A$2:$A$30028;MATCH(($A$1:$A$30);$B$2:$B$30028;0))

my list of numbers from "datasheet"
my new "random" list which i need to assign number
my list of names from "datasheet"

Dont want to start a new thread, so one more question, maybe you can help me also.
I have long name like Lemon from x country, big and sweet. How i can make this name limit like 3 words and put * in the end.

Ex: Lemon from x*?

 

[Oscar Cronquist]

Try this formula:

=LEFT(INDEX($A$2:$A$30028;MATCH(($A$1:$A$30);$B$2:$B$30028;0)),SEARCH("|",SUBSTITUTE(INDEX($A$2:$A$30028;MATCH(($A$1:$A$30);$B$2:$B$30028;0))," ","|",3))-1)&"*"