Match word and Add to next column

[shersu]

Hi all,
One of the managers in my company came out with something that he wants to be done in Excel. It goes like this.

He has an Excel file that contains a list of some English words in one column and its translation into Arabic in the next column. This data is in Sheet1. In the rest of the sheets in the same Workbook, he types in these words in one of the columns of these sheets and he wants the corresponding arabic word to be placed automatically. This action can take place either on the click of a button or....nothing comes into my mind. Can anybody please help me on how this can be done?

Thanks.

[shaolang]

Harlow,

There're a few ways to do it, and the easiest method is to use the built-in formula vlookup.

Assuming column A holds the English words, Arabic in column B, the vlookup formula would look like:

=VLOOKUP(D1,Sheet1!$A:$B,2,FALSE)

Look up the documentation on what each parameter means.

The biggest advantage to this solution is that you don't have to write any code and achieve the results you want. However, the disadvantage is that your boss would have copy and paste the formula next to his newly typed cell (although you can always overcome this by copying and pasting it in advance for him )

Hope this helps....

[shersu]

I get this #N/A when I used this formula.

This is the formula.

=VLOOKUP(C5,Sheet1!C4:C15,Sheet1!D415)

It must be because I am not clear with VLOOKUP(). Please help

[shaolang]

Harlow,

The formula you have keyed is wrong. The documentation from Microsoft is

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

• If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
  
• You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
  
• The values in the first column of table_array can be text, numbers, or logical values.
  
• Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

So, your formula would look like:

=VLOOKUP(C5,Sheet1!$C:$D,2,FALSE)

Hope this helps...

[shersu]

Thanks a lot! That works fine and he is too happy.