# Formula for accessing values in a table

• D3=Phil E3=Red F3= what is the formular for F3

Blue Green Red Orange Yellow
Frank 15 1 21 17 6
Joe 8 23 7 14 16
Phil 18 9 2 5 19
Bill 3 20 24 10 22
Charlie 13 12 4 25 11

What is the formula in F3 that would return value from the above table that
represents the intersection of the D3 Name from the Name Column and the E3 color
from the Color Row. The numbers in the table cannot be sorted.
In this case the answer would be 2.

• 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)

## Files

• 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

## Files

Kind Regards, Will Riley

LinkedIn: Will Riley

• 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:

Barbara - aka The Cat Lady :cat:

Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

• 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

Barbara - aka The Cat Lady :cat:

Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

• Re: Formula for accessing values in a table

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

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

Barbara - aka The Cat Lady :cat:

Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

• Re: Formula for accessing values in a table

Dennis

[bp]*[/bp]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!