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

Item name (Sheet 1)	Cost (Sheet 1)	Item Name (Sheet 2)	Cost (Sheet 2)
A		A - description	1.00
B		B - description	1.25
C		C - description	1.50
D		D - description	1.75

<tbody>
</tbody>

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