Can you calculate a cell name? and How to return a value from an adjacent cell?

[HK]

It's been years since I've really dug into a spreadsheet with lots of
formulas. I've forgotten how much fun it is. I used to be a Lotus 1-2-3
Release 1A wizard. That's how long it's been since I've built spreadsheets
with even a medium complexity.

So excuse this if these are dumb questions:

1) Can you name a cell with a formula? For instance, let's say there's an
column for age. Each cell, I want to name by adding FIRSTNAME + LASTNAME +
"AGE". Then I can refer to that cell by name instead of by cell address in
later formulas.

2) How do I return a value from column B based on matching a value in column
A? For instance, if A is NAME and B is AGE, in a formula, I want to say
"get a person's age if NAME is equal to N". Just a function name would
suffice. I'm pretty good at figuring things out once I get a pointer.

TIA,

HK

 

[Guest]

Question 1: You can use named ranges in formulas.

Question 2: = IF(A1="NAME",B1,"")

Dave

 

[Guest]

Under the Insert menu option there is a name function that will allowing you
to cross reference a name to a value.

 

[HK]

I want the "NAME" to be derived from a formula. Assuming A1 and B1 are text
values, I want the name of cell C1 to be A1&B1.

 

[vezerid]

For question 1:

I don;t think you can define a name as per your desires. You actually
need a formula:

=INDEX(what,MATCH(1,(first="Kostis")*(last="Vezerides")*(age=45),0))

it is assumed that what, first, last and age are named ranges of the
same length (or you can use the actual range references). This is an
*array* formula hence it must be commited with Ctrl+Shift+Enter.

Question 2 is a simpler version of the multicriteria formula that I
gave you:

=INDEX(phones,MATCH("myname",names,0))

VLOOKUP is a more compact version of the 2nd formula (only). If you
are only referring to a 2-col table (A:B) you can lookup the phone
number with:

=VLOOKUP("myname",A:B,2,FALSE)

HTH
Kostis Vezerides