Input sheet with INDEX formula
 

Hi again all:
I have Input sheet with two parts information, its first part related to
Works sheet has no problem. But the second part related to Material sheet
doesn't work correctly.
Input sheet part two:
A37:A69 Material Names(Text).
B36:F36 Seller Names (Text).
B37:F69 Material Price (Value).
These are related to Materials Sheet which is like:

A2:A34 Same Material's Names.
B2:B34 Dropdown list of sellers.
C2:C34 Respective seller price in Dropdown list cell.

I used this
=INDEX(Input!$A$37:$F$69,Match($A2,Input!$A37:$69, 0),MATCH($B2,Input!$36:$36,0))

Merely C2 price was shown correctly rest up to C34 with #REF.

Could you instruct me please.
Cheers.

Input sheet with INDEX formula
 

Use
=INDEX(Input!$A$37:$F$69,MATCH($A2,Input!$A$37:$A$ 69,0),MATCH($B2,Input!$36:$36,0))

You need absolute reference for A37:A69... btw the formula given by you was
missing a $ sign too for it to work in C2...

"Iraj" wrote:

Hi again all:
I have Input sheet with two parts information, its first part related to
Works sheet has no problem. But the second part related to Material sheet
doesn't work correctly.
Input sheet part two:
A37:A69 Material Names(Text).
B36:F36 Seller Names (Text).
B37:F69 Material Price (Value).
These are related to Materials Sheet which is like:

A2:A34 Same Material's Names.
B2:B34 Dropdown list of sellers.
C2:C34 Respective seller price in Dropdown list cell.

I used this
=INDEX(Input!$A$37:$F$69,Match($A2,Input!$A37:$69, 0),MATCH($B2,Input!$36:$36,0))

Merely C2 price was shown correctly rest up to C34 with #REF.

Could you instruct me please.
Cheers.

Input sheet with INDEX formula
 

Thanks Sheeloo, Now it works correctly.

Iraj.

"Sheeloo" wrote:

Use
=INDEX(Input!$A$37:$F$69,MATCH($A2,Input!$A$37:$A$ 69,0),MATCH($B2,Input!$36:$36,0))

You need absolute reference for A37:A69... btw the formula given by you was
missing a $ sign too for it to work in C2...

"Iraj" wrote:

Hi again all:
I have Input sheet with two parts information, its first part related to
Works sheet has no problem. But the second part related to Material sheet
doesn't work correctly.
Input sheet part two:
A37:A69 Material Names(Text).
B36:F36 Seller Names (Text).
B37:F69 Material Price (Value).
These are related to Materials Sheet which is like:

A2:A34 Same Material's Names.
B2:B34 Dropdown list of sellers.
C2:C34 Respective seller price in Dropdown list cell.

I used this
=INDEX(Input!$A$37:$F$69,Match($A2,Input!$A37:$69, 0),MATCH($B2,Input!$36:$36,0))

Merely C2 price was shown correctly rest up to C34 with #REF.

Could you instruct me please.
Cheers.

Input sheet with INDEX formula
 

Hi,

Although referncing an entire row won't hurt, referencing entire columns or
row can require more computer power, the formulas may take longer to
calculate. Also, it appears that you are copying the formula down but not to
the right, in which case you can eliminate the column absolutes. You might
think about:

=INDEX(A$37:F$69,MATCH(A2,A$37:A$69,),MATCH(B2,B$3 6:F$36,))

This is not to say that Sheeloo's answer won't work.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Iraj" wrote:

Hi again all:
I have Input sheet with two parts information, its first part related to
Works sheet has no problem. But the second part related to Material sheet
doesn't work correctly.
Input sheet part two:
A37:A69 Material Names(Text).
B36:F36 Seller Names (Text).
B37:F69 Material Price (Value).
These are related to Materials Sheet which is like:

A2:A34 Same Material's Names.
B2:B34 Dropdown list of sellers.
C2:C34 Respective seller price in Dropdown list cell.

I used this
=INDEX(Input!$A$37:$F$69,Match($A2,Input!$A37:$69, 0),MATCH($B2,Input!$36:$36,0))

Merely C2 price was shown correctly rest up to C34 with #REF.

Could you instruct me please.
Cheers.

Input sheet with INDEX formula.
 

Hi Shane:
In fact I got other formula from Stefi days ago for my sheets and I changed
some part to use here, as I changed a lot in other sheet which were working
properly, but not here.
I used your formula but the result was as such #N/A. I checked every thing
but the same result.

Thanks Iraj.