Help with look up formula

[aysam]

I have two columns : one with names in Arabic and the other with names in English which are sorted alphabetically
I used ( look up ) formula but it returns wrong results

=LOOKUP(G$1:G$100;$A$1:$A$100;$B$1:$B$100)

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 72"]
<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72, align: right"]احمد[/TD]
[/TR]
[TR]
[TD="align: right"]علي[/TD]
[/TR]
[TR]
[TD="align: right"]عمرو[/TD]
[/TR]
[TR]
[TD="align: right"]خالد[/TD]
[/TR]
[TR]
[TD="align: right"]محمود[/TD]
[/TR]
[TR]
[TD="align: right"]محمد[/TD]
[/TR]
[TR]
[TD="align: right"]محمد[/TD]
[/TR]
[TR]
[TD="align: right"]منير[/TD]
[/TR]
[TR]
[TD="align: right"]هاني[/TD]
[/TR]
[TR]
[TD="align: right"]هاني[/TD]
[/TR]
[TR]
[TD="align: right"]هاشم[/TD]
[/TR]
[TR]
[TD="align: right"]سعيد[/TD]
[/TR]
[TR]
[TD="align: right"]وليد[/TD]
[/TR]
[TR]
[TD="align: right"]زغلول[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<colgroup><col width="72" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72, align: left"]Ahmed[/TD]
[/TR]
[TR]
[TD="align: left"]Ali[/TD]
[/TR]
[TR]
[TD="align: left"]Amr[/TD]
[/TR]
[TR]
[TD="align: left"]Khaled[/TD]
[/TR]
[TR]
[TD="align: left"]Mahmoud[/TD]
[/TR]
[TR]
[TD="align: left"]Mohamed[/TD]
[/TR]
[TR]
[TD="align: left"]Mohamed[/TD]
[/TR]
[TR]
[TD="align: left"]Moneer[/TD]
[/TR]
[TR]
[TD="align: left"]Hani[/TD]
[/TR]
[TR]
[TD="align: left"]Hani[/TD]
[/TR]
[TR]
[TD="align: left"]Hashim[/TD]
[/TR]
[TR]
[TD="align: left"]Saeed[/TD]
[/TR]
[TR]
[TD="align: left"]Waleed[/TD]
[/TR]
[TR]
[TD="align: left"]Zaghloul[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]ا[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]الاسم بالعربي[/TD]
[/TR]
[TR]
[TD="align: right"]محمد[/TD]
[/TR]
[TR]
[TD="align: right"]هاني[/TD]
[/TR]
[TR]
[TD="align: right"]زغلول[/TD]
[/TR]
[TR]
[TD="align: right"]سعيد[/TD]
[/TR]
[TR]
[TD="align: right"]عمرو[/TD]
[/TR]
[TR]
[TD="align: right"]محمد[/TD]
[/TR]
[TR]
[TD="align: right"]هاني[/TD]
[/TR]
[TR]
[TD="align: right"]وليد[/TD]
[/TR]
[TR]
[TD="align: right"]خالد[/TD]
[/TR]
[TR]
[TD="align: right"]علي[/TD]
[/TR]
[TR]
[TD="align: right"]منير[/TD]
[/TR]
[TR]
[TD="align: right"]هاشم[/TD]
[/TR]
[TR]
[TD="align: right"]احمد[/TD]
[/TR]
[TR]
[TD="align: right"]محمود[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]الاسم بالانجليزي[/TD]
[/TR]
[TR]
[TD="align: left"]Mohamed[/TD]
[/TR]
[TR]
[TD="align: left"]Nofel[/TD]
[/TR]
[TR]
[TD="align: left"]Ahmed[/TD]
[/TR]
[TR]
[TD="align: left"]Saeed[/TD]
[/TR]
[TR]
[TD="align: left"]Amr[/TD]
[/TR]
[TR]
[TD="align: left"]Mohamed[/TD]
[/TR]
[TR]
[TD="align: left"]Nofel[/TD]
[/TR]
[TR]
[TD="align: left"]Waleed[/TD]
[/TR]
[TR]
[TD="align: left"]Ahmed[/TD]
[/TR]
[TR]
[TD="align: left"]Ali[/TD]
[/TR]
[TR]
[TD="align: left"]Moneer[/TD]
[/TR]
[TR]
[TD="align: left"]Nofel[/TD]
[/TR]
[TR]
[TD="align: left"]Ahmed[/TD]
[/TR]
[TR]
[TD="align: left"]Mahmoud[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jasonb75]

If the name is missing from the list then lookup will give you the closest match found. To ignore the missing names you need vlookup, or index and match.

=IFERROR(VLOOKUP(G1;$A$1:$B$100;2;0);"No Match")

Unless the Arabic names sort differently, the list is not in alphabetical order as you stated.

 

[aysam]

Thanks for help
The name is already in the list in Arabic and in English
But my formula returns it wrongly
and your formula returns " No Match"
Awaiting

 

[jasonb75]

If the formula returns no match then that means that there is no match.

In the example that you provided, the only error that I can see is 'Nofel', which is not in the iist on the left.

 

[aysam]

Sorry for the wrong data in the table above

If the formula returns no match then that means that there is no match.

In the example that you provided, the only error that I can see is 'Nofel', which is not in the iist on the left.

If the formula returns no match then that means that there is no match.
That's right
I look for (هاني) in English Which is ( Hani )but my formula returns ( Nofel )and your formula returns ( No Match )
Although there is a match in the correct table of data which is : هاني === Hani
Hope I'm clear
Awaiting and thanks once more

 

[aysam]

Thanks for your help
Seems I was writing the name with extra spaces