excel help needed

[kamjam]

Hi. I don't really use excel at work...until now.  I have been given task and I am stuck.it will take me ages to do it manually, I thought you guys could help me to create formula. so the 1st spreadsheet i have id number (column a), I have to fill store code column (column b).the id number is on another spreadsheet- column a id number column b store code. so now i would have to copy the id number from spreadheet one , search it on spreadheet 2 find the store code and paste it to spreadheet 1 . hope this make sense 

[#2]

Hi Kamila,

All you need is VLOOKUP Function.

please see example I have attached.

[#3]

Hi Jamil

the problem is I don't how to create v lookup formula . 

[#4]

=vlookup(lookup_value,table_array_,col_index_num,false)

• Lookup_value = The cell containing the data you are looking up i.e. D2
• Table_array = The cells containing the data you are trying to find i.e. A2:B5 (Use $ sign before letters and number if copying the formulae otherwise the table will change). The first column must contain the value you are looking up.
• Col_index_num =  The number of columns in the table array where the return data is contained where col 1 = 0, col 2 = 1 etc.
• False = returns exact matches where the data in the table array is not sorted alphanumerically (otherwise it will return the nearest match).
• Make sure all the above are separated by commas as shown.

[#5]

You can lead VLOOKUP from my friend’s youtube channel. The first 8 minutes explains the basic of vlookup.


https://youtu.be/-hJxIMBbmZY

[#6]

HI Ian 

So, it didn't work . I know i am doing something wrong but not sure what. can you have a look at the attachment and see if you can spot the error ? 

[#7]

Kamila

Can you send a screenshot of the data you are trying to lookup. i.e. [Stores MIDS.xlsx]Sheet!$A:$B

[#8]

Hi Ian 

attached. 

[#9 · Accepted solution]

It is obvious that your lookup value is number but stored as text and the data where you are looking for values are numbers stored as number.

so either you need to change the lookup values back to numbers or simply you can revise your formula like this =VLOOKUP(VALUE(B2),................................... rest of reference

simply wrapping B2 with VALUE(B2) will solve it. Also in your formula you are using entire column reference like A:B which slows down your workbook. It is important to use Excel Tables, or only select the ranges with data instead of whole column reference.

[#10]

Kamila

Jamil has got it spot on.

Your first spreadsheet contains text values (left aligned), whereas the second spreadsheet contains numerical values (right aligned). Change the formulae as he suggests and you should be on your way.

It is a pain in the neck when both sets of figures look identical but are actually different!

[#11]

Thanks guys. done it :)

[#12]

You are welcome.

[#13]

yep . and don't know how to change it !

[#14]

This is common issue faced by the Excel users; however this commonly faced when you try copy data from a merged cell range and paste into non-merged cells.

The easy way to resolve this is restart the Excel file, save the Excel workbook > close Excel and the Open the Excel file.

Apart from that try disabling the Macro Express application. Macro Express is a Windows-based application, this permits the users to record, edit and play back mouse and keyboard macros.

And running the Macros Express program in the background causes the errors. So simply check for the background running programs in your Windows OS and disable the Macro Express.

Source: http://www.repairmsexcel.com/blog/resolve-excel-cannot-copy-paste-data-error