Next: SUMIF function
From: trippknightly on 11 Feb 2005 16:31
AFAIK, Excel has no function allowing simultaneous search for 1 value
along leftmost column of a table range and then also search
horizontally across table top row.

1 way to do this is to embed a LOOKUP to figure out how many columns or
rows to index over (in case of VLOOKUP/HLOOKUP respectively).

Anything else?

From: Ken Wright on 11 Feb 2005 16:53
One way is with
INDEX(WholeDataRange,MATCH(xyz,Column_Data,0),MATCH(xyz,Row_Data,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<trippknightly(a)hotmail.com> wrote in message
news:1108157479.781184.11720(a)o13g2000cwo.googlegroups.com...
> AFAIK, Excel has no function allowing simultaneous search for 1 value
> along leftmost column of a table range and then also search
> horizontally across table top row.
>
> 1 way to do this is to embed a LOOKUP to figure out how many columns or
> rows to index over (in case of VLOOKUP/HLOOKUP respectively).
>
> Anything else?
>


From: Gord Dibben on 11 Feb 2005 17:37
tripp

Don't know if this would help.......

If you have your rows and columns named you can use the intersection operation
to find a value.

Or if you have labels, say across row 1 and down column A you can use them to
create an intersection table.

i.e.

A2:E5 have data.

B1:E1 have title1, title2, title3, title4

A2:A5 have item1, item2, item3, item4

Select A1:E5

Insert>Name>Create. Check top row and left column.

Now in H4 enter =title2 item3

Works great for mileage charts and similar operations but might not be
applicable for your needs.


Gord Dibben Excel MVP

On 11 Feb 2005 13:31:19 -0800, trippknightly(a)hotmail.com wrote:

>AFAIK, Excel has no function allowing simultaneous search for 1 value
>along leftmost column of a table range and then also search
>horizontally across table top row.
>
>1 way to do this is to embed a LOOKUP to figure out how many columns or
>rows to index over (in case of VLOOKUP/HLOOKUP respectively).
>
>Anything else?

From: Ken Wright on 11 Feb 2005 20:16
That second xyz should really have been something else, as you likely
wouldn't use the same variable in both places.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" <ken.wright(a)NOSPAMntlworld.com> wrote in message
news:ezLeSQIEFHA.624(a)TK2MSFTNGP09.phx.gbl...
> One way is with
> INDEX(WholeDataRange,MATCH(xyz,Column_Data,0),MATCH(xyz,Row_Data,0))
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> <trippknightly(a)hotmail.com> wrote in message
> news:1108157479.781184.11720(a)o13g2000cwo.googlegroups.com...
> > AFAIK, Excel has no function allowing simultaneous search for 1 value
> > along leftmost column of a table range and then also search
> > horizontally across table top row.
> >
> > 1 way to do this is to embed a LOOKUP to figure out how many columns or
> > rows to index over (in case of VLOOKUP/HLOOKUP respectively).
> >
> > Anything else?
> >
>
>


 | 
Pages: 1
Next: SUMIF function