Searching Product Price from Price List

Kamal Subhani

Board Regular
Joined
Jan 26, 2004
Messages
146
Hi All

In Sheet 2 I have the price list as follows
Column A
Size(in A2) Quality1 (In B2) Quality2 (in C2) Quality3 (in D2) ....till J2
Product A 100 90 80
Product B 90 80 70
Product C 80 70 60
......... ... ... ...
......... ... ... ...
......... ... ... ...

till row 1000


in Sheet 1 i want a formula to retrieve a the product price to retrieved according to the Size and the quality as follows

A2= Size B2=Quality C2=Formula (which I need to copy down


Hope u have understood me problem


Thanks in advance.


Regards


kamal
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Like This?
Book1.xls
ABCDEFGHIJ
1SizeQual1Qual2Qual3Qual4Qual5Qual6Qual7Qual8Qual9
2Prod1200190180170160150140130120
3Prod2190180170160150140130120110
4Prod3180170160150140130120110100
5Prod417016015014013012011010090
6Prod51601501401301201101009080
7Prod6150140130120110100908070
8Prod714013012011010090807060
9
10
11
12SizeQual
13Prod5Qual3140
14
Sheet6
 

Kamal Subhani

Board Regular
Joined
Jan 26, 2004
Messages
146
What will happen if Copy the formula down.

will it work or I have to change


Plz tell me
Kamal
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Change to absolute ref:

=INDEX($B$2:$J$8,MATCH(A13,$A$2:$A$8,0),MATCH(B13,$B$1:$J$1,0))

Then you can copy down.
 

Kamal Subhani

Board Regular
Joined
Jan 26, 2004
Messages
146
Thanx Fair winds


Can It be done by ant other approac
I mean to say without index match


Thanks


Kamal
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,164
Messages
5,768,561
Members
425,481
Latest member
ihumanl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top