Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
....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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
"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 ---- |
#6
|
|||
|
|||
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 ---- |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
Dynamic formulae - similar to lotus 123 for excel | Excel Discussion (Misc queries) | |||
XL2002 Clipboard doesn't paste formulae | Excel Discussion (Misc queries) | |||
Excel and CubeCell Formulae | Excel Discussion (Misc queries) | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions |