Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP formula | Excel Worksheet Functions | |||
Alternative formula to the vlookup formula? | Excel Worksheet Functions | |||
convert vlookup formula to link formula | Excel Worksheet Functions | |||
Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
Using a Vlookup within an IF formula | Excel Discussion (Misc queries) |