IF and VLOOKUP formula
 

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.

IF and VLOOKUP formula
 

Try this formula in C2 on the Material Costs sheet. You can "fill" it down
and it will work for each row on that sheet. Adjust the addresses referenced
on the Sellers sheet to match all the columns/rows you actually use on it.

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

Remember that even if it appears on 2 rows here, it is really one continuous
formula for the cell in the workbook.

Hope this helps.
"Iraj" wrote:

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.

IF and VLOOKUP formula
 

=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.

IF and VLOOKUP formula
 

Thanks Jlatham It works

"JLatham" wrote:

Try this formula in C2 on the Material Costs sheet. You can "fill" it down
and it will work for each row on that sheet. Adjust the addresses referenced
on the Sellers sheet to match all the columns/rows you actually use on it.

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

Remember that even if it appears on 2 rows here, it is really one continuous
formula for the cell in the workbook.

Hope this helps.
"Iraj" wrote:

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.

IF and VLOOKUP formula
 

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.

IF and VLOOKUP formula
 

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.

IF and VLOOKUP formula
 

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.

IF and VLOOKUP formula
 

You are welcome! Thanks for the feedback!
Stefi

€žIraj€� ezt Ã*rta:

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.

IF and VLOOKUP formula
 

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.

IF and VLOOKUP formula
 

Hi Stefi:
You are really GEM, the formula you gave is excellent and made my sheet
perfect and while coping it so easy rather than others which I had. I just
came back to appreciate as I realize the worth of skilled and profisional
brains.
Wish you all the best.Iraj

"Stefi" wrote:

You are welcome! Thanks for the feedback!
Stefi

€žIraj€� ezt Ã*rta:

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.