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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Tomato[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Lemon[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]208[/TD]
[TD]Potato[/TD]
[/TR]
[TR]
[TD]209[/TD]
[TD]Onion[/TD]
[/TR]
[TR]
[TD]210[/TD]
[TD]Cucumber[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]


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)

[TABLE="width: 500"]
<tbody>[TR]
[TD]C[/TD]
[TD]D (need that formula)[/TD]
[/TR]
[TR]
[TD]Potato[/TD]
[TD]208[/TD]
[/TR]
[TR]
[TD]Cucumber[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]101[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]104[/TD]
[/TR]
[TR]
[TD]Tomato[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]and etc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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)&"*"