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

****al 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 :-)
----------------------------------------------------------------------------

"****al shah" wrote in message
...
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

"****al shah" wrote in message
...
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.

****al shah