vlookup and maximum frustration

[benbrown2k]

https://www.dropbox.com/s/f1yz8vt3tn4iglz/Book1.xlsx

hi, don't know if anyone can help, but i and my collegues are defeated. Just a simple vlookup from one sheet to another... except we cant get it to work. Have looked on the net and tried various things but to no avail.

We use this method to update our price files from suppliers and often get this problem. I am pretty much a "i know how to do what i know" level of expertise but if someone could explain why this speadsheet isnt working or what i can do to make this operation simpler then feel free in simple terms how i can do it. What is frustrating more than anything else is that most of the time it works..

thanks in advance

Ben

 

[Asala42]

=VLOOKUP(E2,Sheet1!$B$1:$C$2000,2,0)

 

[benbrown2k]

brilliant... that worked, thank you. Normally I would put in =vlookup(e2,XXX,4,false) xxx being the sheet name. Why did your formula work and mine not??

 

[etaf]

what process do you use to update the table ?

how does sheet 1 change ?

your looking up Column B , but the table is column A,B,C
and returning column D ( 3 = b,c,d ) but the table is only A,B,C

so its looking for the code in column A and not finding it

Try a Index Match
or using the range , or insert a new table

=INDEX(Sheet1!C:C,MATCH(E2,Sheet1!B:B,0))

 

[benbrown2k]

ok, I am not very technical (read beginner).. I receive a new excel spreadsheet when our suppliers have a price update. I open it, then tidy it up a bit, then on page two used the data from other sources to import from our system all the useful fields from our database. then I have always just renamed sheet1 from the supplier as XXX or whatever then put in the vlookup as above and most of the time it must look up the matching manufacturing stock codes and returns the new value, which I then import back into the system. But I cant figure out why it doesnt always work.

I was hoping there was a really easy formula that I can just fire into future sheets and it pretty much just works

thank you so much for your help and time it is very much appreciated

 

[etaf]

if the new price is always formatted in sheet 1 in the same columns then this will work

=INDEX(Sheet1!C:C,MATCH(E2,Sheet1!B:B,0))