using excel as a database... 2

[hopelessliar]

I have a table that looks a bit like this:

Jan Feb Mar
Small red 1 2 5
Medium red 4 3 1
small blue 3 2 1
medium blue 2 4 1


Can I return the value at the intersection of the ranges 'Jan' and 'small red'?

I know I can do this using a combination of index, match and vlookup, but I'm hoping that there's a neater way of doing this maybe using the intersection of named ranges?

Any help greatfully received.

 

[xlbo]

Tools>Options>Calculation
tick "Accept labels in formulae"

then

='Jan' 'Small Red'

will do what you need. Within a formula a {SPACE} is used as an intersect operator. Alternatively, if you have named ranges, you can do it without "Accept labels in formulae"
Lets say your names are "SmallRed", "SmallBlue", "MediumRed", "MediumBlue", "Jan", "Feb" etc. Your formula would be:

=SmallRed Jan

without changing any options

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[hopelessliar]

woo flipping hoo.

Thanks Geoff, that is SO useful

 

[xlbo]

Tell me about it !!!

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[MeGustaXL]

Un-Be-Flippin-Leavable Geoff! How in Hades do you find out this stuff??

Yet another STAR, Ace

Chris

Varium et mutabile semper Excel

 

[xlbo]

really no idea !! Was just browsing for something else in the help file and came across the fact that a space is used as the intersect operator. Doesn't work on seperate sheets though :-(

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question