Lookup in Matrix

[Johannes]

Hello all,

How can I look up a value in a matrix. First column has countries, first row
has years. Am looking for value of specific country in specific year.
Thought about combinations of lookup and matches, but that does not make it
any prettier. Any suggestions?

Any suggestions?

[Leo Heuser]

Hello Johannes

One way:

=INDEX(C2:I9,MATCH("Country",B2:B9,0),MATCH(Year,C1:I1,0))

Data in C2:I9, countries in B2:B9 and years in C1:i1

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Johannes" <Johannes@JvanderPol.nl> skrev i en meddelelse
news:eEWt%23fHUFHA.2872@TK2MSFTNGP14.phx.gbl...
> Hello all,
>
> How can I look up a value in a matrix. First column has countries, first
> row
> has years. Am looking for value of specific country in specific year.
> Thought about combinations of lookup and matches, but that does not make
> it
> any prettier. Any suggestions?
>
> Any suggestions?
>
>

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/.../xindexvba.zip
It's in the "Excel for Lotus 123 Users" section on page:
http://www.bygsoftware.com/examples/examples.htm

In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
workbook shows two Excel formula constructions that achieve the same result.

The first example uses two additional inputs. It uses the Excel functions:
INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
function. It uses the Excel functions: INDEX, MATCH and OFFSET.

There are also two additional pieces of VBA showing how to use this in code
and capture an error condition.

The code is open and commented.

There is also an alternative construction using SUMPRODUCT at:
http://www.bygsoftware.com/Excel/fun...sumproduct.htm


--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy


"Johannes" <Johannes@JvanderPol.nl> wrote in message
news:eEWt%23fHUFHA.2872@TK2MSFTNGP14.phx.gbl...
> Hello all,
>
> How can I look up a value in a matrix. First column has countries, first
row
> has years. Am looking for value of specific country in specific year.
> Thought about combinations of lookup and matches, but that does not make
it
> any prettier. Any suggestions?
>
> Any suggestions?
>
>

[Johannes]

Cheers, works perfectly
"Leo Heuser" <leo.heuser@NOSPAMadslhome.dk> schreef in bericht
news:%23YAKN5HUFHA.3344@TK2MSFTNGP10.phx.gbl...
> Hello Johannes
>
> One way:
>
> =INDEX(C2:I9,MATCH("Country",B2:B9,0),MATCH(Year,C1:I1,0))
>
> Data in C2:I9, countries in B2:B9 and years in C1:i1
>
> --
> Best Regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
> "Johannes" <Johannes@JvanderPol.nl> skrev i en meddelelse
> news:eEWt%23fHUFHA.2872@TK2MSFTNGP14.phx.gbl...
> > Hello all,
> >
> > How can I look up a value in a matrix. First column has countries, first
> > row
> > has years. Am looking for value of specific country in specific year.
> > Thought about combinations of lookup and matches, but that does not make
> > it
> > any prettier. Any suggestions?
> >
> > Any suggestions?
> >
> >
>
>

[Leo Heuser]

You're welcome and thanks for the feedback :-)

LeoH


"Johannes" <Johannes@JvanderPol.nl> skrev i en meddelelse
news:uneT1DIUFHA.2172@tk2msftngp13.phx.gbl...
> Cheers, works perfectly

[Johannes]

Guess the VBA solution is the prettiest. Kind of sad these things are not
standard functions...

Thanks,

Johannes
"Andy Wiggins" <newsgroupsNOT@THISbygsoftware.c o m> schreef in bericht
news:%23PCSB6HUFHA.3944@tk2msftngp13.phx.gbl...
> This file might be a help:
> http://www.bygsoftware.com/examples/.../xindexvba.zip
> It's in the "Excel for Lotus 123 Users" section on page:
> http://www.bygsoftware.com/examples/examples.htm
>
> In Excel there is no direct equivalent for Lotus 123's XINDEX function.
This
> workbook shows two Excel formula constructions that achieve the same
result.
>
> The first example uses two additional inputs. It uses the Excel functions:
> INDEX and MATCH The second example uses the same inputs as the Lotus
XINDEX
> function. It uses the Excel functions: INDEX, MATCH and OFFSET.
>
> There are also two additional pieces of VBA showing how to use this in
code
> and capture an error condition.
>
> The code is open and commented.
>
> There is also an alternative construction using SUMPRODUCT at:
> http://www.bygsoftware.com/Excel/fun...sumproduct.htm
>
>
> --
> Regards
> -
> Andy Wiggins FCCA
> www.BygSoftware.com
> Excel, Access and VBA Consultancy
>
>
> "Johannes" <Johannes@JvanderPol.nl> wrote in message
> news:eEWt%23fHUFHA.2872@TK2MSFTNGP14.phx.gbl...
> > Hello all,
> >
> > How can I look up a value in a matrix. First column has countries, first
> row
> > has years. Am looking for value of specific country in specific year.
> > Thought about combinations of lookup and matches, but that does not make
> it
> > any prettier. Any suggestions?
> >
> > Any suggestions?
> >
> >
>
>