I presume that you are asking for formulas that "needs smaller space" because
of the many cells they have to be put into. With Stefi's formula, (and even
mine), you can 'fill' the formula down the sheet easily. Use Excel's Help
feature and search for FILL to find various ways to quickly fill many cells
with a formula with very few keystrokes.

"Iraj" wrote:

Thanks Jlatham, for paying so much attention, in fact I have long list of
sellers and prices and if you have other formula that needs smaller space I
appreciate it.

"JLatham" wrote:

Stefi's formula has the advantage of not having to constantly update the
formula as you add more seller's names or item names on the second sheet. I
recommend you use it.

"Iraj" wrote:

Thanks Stefi with this also works.

"Stefi" wrote:

=INDEX(Sheet2!$A$1:$D$4,MATCH($A2,Sheet2!$A:$A,0), MATCH($B2,Sheet2!$1:$1,0))

Regards,
Stefi

€žIraj€� ezt Ã*rta:

Hi again all:
I have in sheet 1;(Material Costs)
A B C
1 Items Seller Price
2 Sand Dropdown List
3 Cement Dropdown List
4 Bars Dropdown List

I have in sheet 2;(Sellers)
A B C
D
1 Items Mick Sam
Jim
2 Sand 2$ 3$
2.5$
3 Cement 5$ 5.2$
4.9$
4 Bars 12$ 10.5$
11$

Units are same. What formula in sheet 1, can i write that when dropdown list changes the name the exact price of the related seller's name appears in C2:C4 respectively?

I tried with IF combination with VLOOKUP which result was #NAME?
Thanks for instruction.