Is there a formula for that??

[cookingcow]

I have been searching high and low for a formula that will meet my needs but cant find one! I appreciate any help or guiding light!

I have a spreadsheet set up for my grocery list. Column A contains the name of the product, Column B contains the estimated price of that item, at the very bottom I have the sum of all of the estimated prices for that store. On a separate sheet in the same document I have set up a "common items" list where Column C is the name of that item and Column D is the price when I last bought that item (my 'estimated cost' if you will).

So here is my question:
Is there a formula, similar to the IF function, that will look through my list of common items, say C1:20, and post the information from the cell in the adjacent column.

So if Bread shows up in A1, I want B1 to look through C1:C20 for Bread and if found on C5 post the number from D5.

I hope this makes sense!

 

[cyrilbrd]

Hi, maybe index match as follows INDEX(range_such_as_$A$2:$A$20,MATCH($B2,$C$2:$C$20,0)) thus would compare the data from B2 with the data in the range C2 to C20 and will return the data from A2 to A20 IF there is a match.

If this close to what you needed?

 

[FDibbins]

You could probably do this with a simple vlookup as well...

=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

 

[cookingcow]

You could probably do this with a simple vlookup as well...

=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

That is exactly what I needed! Thank you! Now for my next question you might be able to help me with,
I pre-added the vlookup function to the appropriate cells to find that if there was no relevent data for the vlookup_value I got a #N/A error. I did a quick IF function so that if the vlookup_value was blank to result in a blank cell. Now, if I add an item to the vlookup_value cell that isnt yet added to my "commonly bought" list, otherwise known as my table_array, it responds in kind with #N/A. Im trying to rewrite my formula so that if there is no text written the cell remains blank, if the vlookup comes back with no relevant data the cell remains blank but if the vlookup has an answer then it is shown. Is this possible?

edit:
This is what my formula looks like right now
=if(B3="","",vlookup(B3,Sheet2!B3:C33,2,FALSE))

possibly something like

=if(B3="","",if(B3= SYMBOL_FOR_TEXT???,vlookup(B3,Sheet2!B3:C33,2,FALSE),""))

 

[Aladin Akyurek]

Try to round up with IFERROR...

=IFERROR(VLOOKUP(B3,Sheet2!$B$3:$C$33,2,0),"")