which function is use?

[Shital Shah]

i have price list data like below in sheet1

Item basic-rate
Printers 100
Modems 200
Scanners 300
Plotters 100
Inks 50

with this i have some special price list of some delares

Printers,Modems,Scanners,Plotters,Inks
raju Comp. 120,265,-,-,35,
U'r Soft -,210,-,115,60
Data sys 110,190,340,-,-,

Now i have data in sheet2 where i use vlookup price from
price list sheet1

Month,Qty,Item,Name,Rate,amt

here in rate column i use vlookup function is working but
i want, when there is name comes from special price list
it should take Rate of sprical price.
e.g.

Month,Qty,Item,Name,Rate,amt
April,10,Printer,abc,100,1000
April,10,Inks,U'r Soft,60,600

Any Help.

Thanks in Advance.

Shital Shah

 

[Frank Kabel]

Hi
try something like the following:
=IF(ISNA(INDEX('special_price'!A1:F10,MATCH(D1,'special_price'!A1:A10,0
),MATCH(C1,'special_price'!A1:F1,0))),VLOOKUP(C1,'price_list'!A1:B30,2,
0),INDEX('special_price'!A1:F10,MATCH(D1,'special_price'!A1:A10,0),MATC
H(C1,'special_price'!A1:F1,0)))

 

[Shital Shah]

hi
Thanks Frank Kabel
It's working nicely.

Shital

-----Original Message-----
Hi
try something like the following:
=IF(ISNA(INDEX('special_price'!A1:F10,MATCH (D1,'special_price'!A1:A10,0
A1:A10,0),MATC
H(C1,'special_price'!A1:F1,0)))

--
Regards
Frank Kabel
Frankfurt, Germany




.