IF and VLOOKUP formula

[Iraj]

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$

 

[JLatham]

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.

 

[Stefi]

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

Regards,
Stefi

„Iraj†ezt írta:

 

[Iraj]

Thanks Jlatham It works

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]

Thanks Stefi with this also works.

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

Regards,
Stefi

„Iraj†ezt írta:

 

[JLatham]

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]

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.

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Iraj†ezt írta:

 

[JLatham]

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]

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