Which function use?

  • Thread starter Thread starter shital
  • Start date Start date
S

shital

i have a 2 table like below.
Table1
A B C J
1 Name of co.,Prodcut1,Product2,..... up Product10
2 Compmany1 4000,5000,
3 Compmany2 2500,1000,
4 Compmany3
5 upto 10.

Table2
A B c J
1 Name of co.,Prodcut1,Product2,..... up Product10
2 Compmany1 1.25, 3
3 Compmany2 4,2.50
4 Compmany3
5 upto 10.

what i want is
I have qty sold in table1 and comm. on Table2

When i type Product1 & Company1, It should Multiply 4000
(From table1)*1.25 (from table2) and give me ans. =5000
OR
When i type Product2 & Company1, IT should Multiply 5000
(From table1)*1.25 (from table2) and give me ans. =6250

any help.

Shital Shah
 
Shital,

If your first table is in cells A1:K11 on sheet "Table 1" and your second
table is in cells A1:K11 on sheet "Table 2", and you enter the company name
on cell A1 and the product in cell A2 of the current sheet, then this
formula (which should be entered without line breaks) will give you what you
want:

=OFFSET('Table 1'!A1,MATCH(A1,'Table 1'!A2:A11,FALSE),
MATCH(A2,'Table 1'!B1:K1,FALSE)) *
OFFSET('Table 2'!A1,MATCH(A1,'Table 2'!A2:A11,FALSE),
MATCH(A2,'Table 2'!B1:K1,FALSE))

HTH,
Bernie
MS Excel MVP
 
Shital,

My previous message seems to have gone astray, so my apologies for any
double posting.

If your tables are in cells A1:K11 on sheets "table 1" and "Table 2", then
this formula will look up the compay name entered in cell A1 and the product
entered in cell A2 of the current sheet:

=OFFSET('Table 1'!A1,MATCH(A1,'Table 1'!A2:A11,FALSE),
MATCH(A2,'Table 1'!B1:K1,FALSE))*
OFFSET('Table 2'!A1,MATCH(A1,'Table 2'!A2:A11,FALSE),
MATCH(A2,'Table 2'!B1:K1,FALSE))

This should all be on one line, so that there aren't any line breaks in the
formula.

HTH,
Bernie
MS Excel MVP
 
One way:

Assume your entry cells are D1 (Product) and D1 (Company). Then use
(array entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(OFFSET(Table1,1,1,10,10)*OFFSET(Table2,1,1,10,10),
MATCH(D1,INDEX(Table1,,1),FALSE)-1, MATCH(D2,INDEX(Table1,1,),FALSE)-1)

Note: Table1 and Table2 should be defined as, for example, A1:K11 for a
10 product, 10 company matrix.
 
Where do you want your results? and could you clearify a
bit more on 'When i type Product1 & Company1' where are
you typing, are u creating a new table for ur results??
 
Back
Top