Automatically look up values across multiple sheets

[Enzo_Matrix]

We are creating price books and the data is currently being updated manually one item at a time. I am hoping to be able to do a data dump with the new costs from our data base and have our price books update automatically.

I have 2 sheets with data in 2 of the columns in each sheet
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Item name (Sheet 1)[/TD]
[TD]Cost (Sheet 1)[/TD]
[TD]Item Name (Sheet 2)[/TD]
[TD]Cost (Sheet 2)[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]A - description[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]B - description[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]C - description[/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD]D - description[/TD]
[TD]1.75[/TD]
[/TR]
</tbody>[/TABLE]

I am wondering if there is something that will look at "Item Name (Sheet 1)", take the letter/word from those cells and and then reference it in "Item Name (Sheet 2)" and then pull "Cost (Sheet 2)" and input it into "Cost (Sheet 1)".

Please help

 

[Yongle]

Try this

=INDEX(Sheet2!B:B,MATCH(A2&"*",Sheet2!A:A,0))


Assumes Item name is in column A and cost is in column B iin BOTH sheets

 

[Enzo_Matrix]

I did try it however it returned a value of '0'.

I also changed the 0 at the end of the formula to a 1 as it will not be an exact match. Sheet 1 only has a partial name.

 

[Yongle]

I also changed the 0 at the end of the formula to a 1 as it will not be an exact match. Sheet 1 only has a partial name.

That will not work for 2 reasons
- my formula is looking for an exact match ( * is a wildcard )
- non-exact match is for numbers not text

This is what my formula is doing:

Where is it looking for a match?
in column A in sheet 2
How is it matching?
- it is looking for sheet1 cell value followed by any other characters

So it looks for
apple
banana
carrot
damson

and will return a match for all of these
apple -a hard fruit
banana - a yellow fruit
carrot - a pointy vegetable
damson - a tasty fruit

What is different in your data?

 

[Yongle]

I should have said in the previous post

So it looks for (= values in sheet1)
apple followed by any character(s)
bananafollowed by any character(s)
carrot followed by any character(s)
damson followed by any character(s)

and will return a match for all of these (= values in sheet2)
apple -a hard fruit
banana - a yellow fruit
carrot - a pointy vegetable
damson - a tasty fruit