Formulae

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
....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
 
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 said:
... use of the { ; and 2,0 in this formulae.
Click to expand...

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.
 
Back
Top