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

[Guest]

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

 

[JulieD]

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

 

[Guest]

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.

 

[Ken Wright]

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

 

[Guest]

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]

Apologies, as I gave you a bum steer there and missed off a part of the
formula. Lets walk through so you understand what it's doing and then you
can see where I screwed up (Or you can just skip to the end and use the
amended formula).

In the formula you have, can you see that when you edit the formula, the
ranges actually pick up the right data in each table. The ranges don't have
to be the same dimensions in each table, although it shouldn't make any
difference if they are. The trick is to get one working formula on one of
your tables and then simply replicate it, but changing ranges, eg using the
example I gave you

I created 2 dummy tables of data, and then the first formula I created was
against the first dummy table of data I had

Table1 (Formula1)
INDEX($4:$17,MATCH(B2,$A$4:$A$17,0),MATCH(B1,$4:$4,0))

I then tried this with some sample data that I knew was in that first table
and made sure that it did return the values I expected. By clicking into
the formula you should see the ranges on the sheet itself highlighted, and
that just gives you a indication that your formula is looking at the right
places. Once you have this working, copy the formula and paste it into
another cell, and then edit the formula such that it now looks at your
second table instead of the first.

Table2 (Formula2)
INDEX($20:$33,MATCH(B2,$A$20:$A$33,0),MATCH(B1,$20:$20,0))

Once again try it with dummy data that you know is in your second table and
ensure that it does actually return the values you expect.

Now what you need to do is simply combine the two, and the logic you are
going to apply is as follows (and this simple bit is where I screwed up)

I had told you

=IF(ISNA(Formula1),Formula2)

whereas in fact it should have been

=IF(ISNA(Formula1),Formula2,Formula1)

based on =IF(Condition, If_True_Do_This,If_False_Do_This) with Condition
being whether formula1 produces an #N/A error or not.

This says that if formula one produces a result, the ISNA(...) evaluates to
FALSE, so the IF formula will go to the If_False_Do_This bit. If formula1
produces an error because it is not in that table, then the IF formula will
go to the If_True_Do_This bit.

You now just combine the formulas as per the example, such that

=IF(ISNA(Formula1),Formula2,Formula1) becomes

=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)),INDEX($4:$17,MATCH(B2,$A$4:$
A$17,0),MATCH(B1,$4:$4,0)))

Sorry if a bit long winded, but I think it helps to know what it is actually
doing, and apologies for the bum steer in the first place.