Arrays/vlookup???

[Ian]

Hi I am very much an amateur with excel I only really use it to build product
sheets and recipe templates. I am basically trying to do this and I can't
seem to figure it out ( I don't speak excel too well)....

I have a recipe card on sheet 2
In cell A10 I would type in an ingredient "flour"
and what I would like it to do is put my cost per/g in cell g10 which is
linked to my master price list
here is the catch I have over 300+ recipes and I don't want to manually link
all of my G cells with the master price list
I would like to copy and paste my recipes into my current template and link
corresponding words with the appropriate cell on a different sheet. Is this
possible?

 

[Ashish Mathur]

Hi,

You could use the VLOOKUP() function here. Please read up on the same in
the Help menu.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[dlotz]

OK you want to type in the indgredent in one cell and then the
cost pe grams will auto popluate in another cell.
OK on sheet two column A list you indg. in column list the cost per gram.

ok so in sheet
lets say col a is the ingredent and colm b is the grams.
in column b typ
=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE)),"",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

this set to have 500 indg

 

[Ian]

OK you want to type in the indgredent in one cell and then the
cost pe grams will auto popluate in another cell.
OK on sheet two column A list you indg. in column list the cost per gram.

ok so in sheet
lets say col a is the ingredent and colm b is the grams.
in column b type
=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE)),"",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

this set to have 500 indg


OK this is still a bit confusing let me show you what i was trying to do which didn't work this is an example....

this look up was in my recipe card temple on sheet 2

=LOOKUP(A10{"Almond","Bananas","Bananas2","Butter","Chocdark","Cream","Currants","Flour",â€Honey","Cinamon","Bpowder","Milk","Pecan","Strawberry","Sugar","Vanilla","Walnut","Yeast"},{Sheet1!H11,Sheet1!H3,Sheet1!H5,Sheet1!H28,Sheet1!H31,Sheet1!H13,Sheet1!H6,Sheet1!H30,Sheet1!H14,Sheet1!H15,Sheet1!H16,Sheet1!H17,Sheet1!H18,Sheet1!H19,Sheet1!H21,Sheet1!H20,Sheet1!H29,Sheet1!H7,Sheet1!H22})

I was using my recipe template(sheet 2) on a different sheet then my master
price list(sheet 1) so i wanted the cells in G 1-50*(sheet 2) on my recipe
card to be linked to the price on the master price list I was also hoping to
be able to just type in an ingredient on my recipe card template in cell A
1-50*(sheet 2) and it would automatically link my G cells(sheet 2).
Thanks ian

 

[Ian]

OK you want to type in the indgredent in one cell and then the
cost pe grams will auto popluate in another cell.
OK on sheet two column A list you indg. in column list the cost per gram.

ok so in sheet
lets say col a is the ingredent and colm b is the grams.
in column b type
=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE)),"",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

this set to have 500 indg

but it needs to gather that info from cell H 1-1000* on sheet 1(master price
list)

=LOOKUP(A10{"Almond","Bananas","Bananas2","Butter","Chocdark","Cream","Currants","Flour",â€Honey","Cinamon","Bpowder","Milk","Pecan","Strawberry","Sugar","Vanilla","Walnut","Yeast"},{Sheet1!H11,Sheet1!H3,Sheet1!H5,Sheet1!H28,Sheet1!H31,Sheet1!H13,Sheet1!H6,Sheet1!H30,Sheet1!H14,Sheet1!H15,Sheet1!H16,Sheet1!H17,Sheet1!H18,Sheet1!H19,Sheet1!H21,Sheet1!H20,Sheet1!H29,Sheet1!H7,Sheet1!H22})

If I use this

=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE)),"",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

and change my master price list cell order from H (price per g)
to B (price per gram) how would i get that info to correlate to a diff sheet??
thanks Ian

 

[dlotz]

that is one very long formula. it tought to understand
if you like ill take a look at it
(e-mail address removed)

 

[Ian]

Hi
just wanted to thank you for all the help the formula worked perfectly!!
Cheers,
Ian