Re: How to do two way lookup for more than 400 row & Coulmn?

From: shital shah (shitalshah_at_discussions.microsoft.com)
Date: 02/06/05 

Date: Sun, 6 Feb 2005 01:41:02 -0800

Thanks Ken
It's working with individual Table formula but when i use with two tables it
display false.

i have layout of data like....

table1
        item1 item2 item3 item4 item5
abc 3 5 78 9
bbc 4 54 6 7
cnb 6 6 5
db 65 4
eff 7

table2

        item256 item257 item258 item259 item260
abc 3.5 6 5
bbc 4.7 78 6
cnb 8.9 9 8 9
db
eff 5.7 8

when i work with exm. format it's display false.

=IF(ISNA(INDEX($4:$17,MATCH(B2,$A$4:$A$17,0),MATCH(B1,$4:$4,0))),INDEX($20:$33,MATCH(B2,$A$20:$A$33,0),MATCH(B1,$20:$20,0)))

if i write in b1 item256 and b2 abc it work but if i write in b1 item2 and
b2 abc i display false.

pls tell me where I am wrong or i have to change the layout.

thanks
regards

shital shah

"Ken Wright" wrote:

> You can use 2 tables. There are no more than 256 columns and that cannot be
> changed.
>
> Use 2 INDEX/MATCH queries, one for each table, checking table 1 first and
> then using IF(ISNA(...) to trap for the error if it doesn't appear in table
> 1 and then as a result do a search of table 2, eg
>
> Table1 (Formula1)
> INDEX($4:$17,MATCH(B2,$A$4:$A$17,0),MATCH(B1,$4:$4,0))
>
> Table2 (Formula2)
> INDEX($20:$33,MATCH(B2,$A$20:$A$33,0),MATCH(B1,$20:$20,0))
>
> -IF(ISNA(Formula1),Formula2)
>
> =IF(ISNA(INDEX($4:$17,MATCH(B2,$A$4:$A$17,0),MATCH(B1,$4:$4,0))),INDEX($20:$
> 33,MATCH(B2,$A$20:$A$33,0),MATCH(B1,$20:$20,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 :-)
> ----------------------------------------------------------------------------
>
> "shital shah" <shitalshah@discussions.microsoft.com> wrote in message
> news:79690F0A-4668-4FAB-8C1C-19D9DE98875A@microsoft.com...
> > Thanks for reply
> >
> > I have Data layout like
> >
> > A B C D
> > 1 Name item1 item2 item3 like
> > 2 Raj & co. 5.6 4.5 7.8
> > 3 Tom & Tom 4.5 9.6 2.7
> > 4 Bits & Bits 1.6 8.9 1.4
> > if i use index & match function it's works for upto 256 column,
> > ex. i want rate for item2 & party name raj & co. useing index & match
> > function i get 4.5
> > but now i have more than 256 items pls help.
> > "JulieD" wrote:
> >
> > > Hi
> > >
> > > i'm a bit confused about your layout
> > > do you have
> > > ........A............B........C
> > > 1...Party.......Item.....Rate
> > > 2...Party1......Item1....50
> > > 3...Party2......Item1....74
> > >
> > > etc
> > > if so
> > > then if you put in D1 the party you're interested in and in E1 the item
> > > the following formula will return the rate for you
> > > =INDEX(A2:C500,MATCH(D1&E1,A2:A500&B2:B500,0),3)
> > > note - it is an array formula so you need to enter it with control &
> shift &
> > > enter not just enter
> > >
> > > Regards
> > > JulieD
> > >
> > > "shital shah" <shitalshah@discussions.microsoft.com> wrote in message
> > > news:2E99C2DA-0FB8-4C51-94FE-256D2B268F10@microsoft.com...
> > > > hi to all
> > > > how do i match the party name & item to find rate of perticular party
> for
> > > > perticular item.
> > > > I want to do two way lookup, but there are not more than 256 column
> > > >
> > > > I have data like
> > > > NameofParty in ROWs more than 500 and item1, item2,
> item3,...........more
> > > > than 400 in columns. which way i can find rate of party with item
> pls.
> > > > help
> > > > me.
> > > >
> > > > shital shah
> > > >
> > >
> > >
> > >
>
>
>