Formula for accessing values in a table

Re: Formula for accessing values in a table

Welcome to OzGrid

You can use the Intersection operator in Excel (a space) if you create names for the table. >>> Select the table (cells A&:F12 in the attached) and from the tool bar do a INSERT > NAME > CREATE > make sure "Top Row" and "Left Column" are checked. Your row and column labels are now usable names.

In F3 you can enter either
=Phil Red

or if you want to use references to the entries in D3 and E3
=INDIRECT(D3) INDIRECT(E3)

Re: Formula for accessing values in a table

I have attached an example workbook

Assuming your data table is A1:F6

=INDEX(A1:F6,MATCH(A9,A1:A6,0),MATCH(B9,A1:F1,0))

If A9 is Phil and B9 is Red

Re: Formula for accessing values in a table

Quote from thomach

You can use the Intersection operator in Excel (a space) if you create names

Cool thomach. I had seen that used in another post recently and thought I'd have to investigate that and what it was all about. Your post made it clear and you attachment was most helpful.

I'd never used that method to name the ranges in a table before. :rock:

Re: Formula for accessing values in a table

Hi, Barb,

EDIT: I thought the below ( did it at some point in the distant past, but went back to double check it just now am am having touble setting it up corectly. THERE IS a way to do it, but I'm forgetting something basic. The setup routine must differ.)

Incidently, you also can select "Label" instead of "Create" and it works the same way, EXCEPT it does not create Named ranges that clutter up the Names List (if you have a bunch of them there already).

Re: Formula for accessing values in a table

Yes thomach I'm having trouble as well with the Label part of naming ranges and the Help isn't much HELP!

Let me know what you come up with. I'm using Excel 2000.

Barbara

Re: Formula for accessing values in a table

Searching the Internet I came up with the following from MS:

Quote

You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) to represent cells, ranges of cells, formulas, or constant (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name.

You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.

Use labels as references

This option must be set when you first create a workbook, or you will need to re-enter the formulas that use labels.

• Click Options on the Tools menu, and then click the Calculation tab.
  

  
  

• Under Workbook options, select the Accept labels in formulas check box.
  

  
  

• To re-enter a formula that used a label before setting this option, select the cell that contains the formula, press F2, and then press ENTER.

Note To have this option recognized by default for all new workbooks, create a new workbook, follow the procedure above, and save the setting in the template book.xlt.

Display More

So you either have it turned on before you create the formula that references labels or turn it on and edit the cell.

Re: Formula for accessing values in a table

Dennis

[bp]*[/bp]