Formulae
 

Hi All,
The Cells B4:B30 contain the text North,South,East,West.
North=Bjorn Rasmussen, South=Pablo Garcia, East=Marie Jones and West=James
Miller. How do I enter the formulae in cell C4 to show the relivant name with
relivant compass point.

Thanks,

kfh.

One way via VLOOKUP

Put in C4 and copy down to C30:

=VLOOKUP(B4,{"North","Bjorn Rasmussen";"South","Pablo Garcia";"East","Marie
Jones";"West","James Miller"},2,0)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"k f h" wrote in message
...

Hi All,
The Cells B4:B30 contain the text North,South,East,West.
North=Bjorn Rasmussen, South=Pablo Garcia, East=Marie Jones and West=James
Miller. How do I enter the formulae in cell C4 to show the relivant name
with
relivant compass point.

Thanks,

kfh.

....more ways:

=INDEX({"Bjorn Rasmussen","Pablo Garcia","Marie Jones","James
Miller"},MATCH(B4,{"North","South","East","West"}, 0))

or

=CHOOSE(MATCH(B4,{"North","South","East","West"},0 ),"Bjorn Rasmussen","Pablo
Garcia","Marie Jones","James Miller")

Regards,
KL


"Max" wrote in message
...
One way via VLOOKUP

Put in C4 and copy down to C30:

=VLOOKUP(B4,{"North","Bjorn Rasmussen";"South","Pablo
Garcia";"East","Marie
Jones";"West","James Miller"},2,0)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"k f h" wrote in message
...

Hi All,
The Cells B4:B30 contain the text North,South,East,West.
North=Bjorn Rasmussen, South=Pablo Garcia, East=Marie Jones and
West=James
Miller. How do I enter the formulae in cell C4 to show the relivant name
with
relivant compass point.

Thanks,

kfh.

Hi All,

Thanks for the rapid response, I tried them both. Can you explain to me the
use of the { ; and 2,0 in this formulae.

Thanks,

kfh.


"k f h" wrote:


Hi All,
The Cells B4:B30 contain the text North,South,East,West.
North=Bjorn Rasmussen, South=Pablo Garcia, East=Marie Jones and West=James
Miller. How do I enter the formulae in cell C4 to show the relivant name with
relivant compass point.

Thanks,

kfh.

"k f h" wrote
... use of the { ; and 2,0 in this formulae.

In the VLOOKUP, what's appearing within the curly braces { } is the 2nd
param, the Table_array. For a small table array, like in this instance, a 4R
x 2C table which houses the items:

North Bjorn Rasmussen
South Pablo Garcia
East Marie Jones
West James Miller

we could simply choose to insert the entire table_array into the function
itself, making it effectively a "standalone" table_array so as to speak,
hence doing away with having to create the table in say, Sheet2's A1:B4, and
referencing it as: Sheet2!$A$1:$B$4
But for larger table_arrays, for ease of maintenance, the latter option
would be the usual choice.

The "2" is the 3rd param, the col_index_num, which specifies that the return
is to be from the 2nd col of the table_array (where the names are located).

The "0" is the 4th param, the range_lookup, which specifies to find an exact
match (inserting a zero is the same as putting: FALSE as the 4th param, but
shorter).

For more info, check up on VLOOKUP in Excel Help.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max,

Thanks again for your help, that makes it clearer.

kfh.


"Max" wrote:

"k f h" wrote
... use of the { ; and 2,0 in this formulae.

In the VLOOKUP, what's appearing within the curly braces { } is the 2nd
param, the Table_array. For a small table array, like in this instance, a 4R
x 2C table which houses the items:

North Bjorn Rasmussen
South Pablo Garcia
East Marie Jones
West James Miller

we could simply choose to insert the entire table_array into the function
itself, making it effectively a "standalone" table_array so as to speak,
hence doing away with having to create the table in say, Sheet2's A1:B4, and
referencing it as: Sheet2!$A$1:$B$4
But for larger table_arrays, for ease of maintenance, the latter option
would be the usual choice.

The "2" is the 3rd param, the col_index_num, which specifies that the return
is to be from the 2nd col of the table_array (where the names are located).

The "0" is the 4th param, the range_lookup, which specifies to find an exact
match (inserting a zero is the same as putting: FALSE as the 4th param, but
shorter).

For more info, check up on VLOOKUP in Excel Help.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"k f h" wrote in message
...
Hi Max,

Thanks again for your help, that makes it clearer.

kfh.