Vlookup

[joeloveszoe]

hi!
i have been using microsoft access to combine spreadsheets for years
i am now looking for a new strategy

i have 2 spreadsheets to 'combine"
the common factor in my 2 spreadsheets is sku & stock number (same)

do the 2 sets of data have to be in the same workbook?
what is the easiest way to /'combine all data into one
and is there a way to find what skus (and the rest of the corresponding info) is on one spreadsheet and not on the other)

thanks in advance for the help!

 

[Joe4]

can you suggest videos or online training sessions or help me find someone one on one to work with someone to teach me how to do multiple vlookups as you are suggesting here? do you offer this service?

Back up in post 6, your reply seemed to imply that you know how to use VLOOKUP (or are at least somewhat familiar with it). Is that not the case?
In post 7, I showed how you can structure these VLOOKUP formulas to suppress the errors, or return messages instead of the values.

Really, doing multiple VLOOKUPs is not any harder than doing one. You are just doing the same thing, over-and-over (as Excel formulas can only return values to the cells that they are in, and not to multiple cells at once).

If you are wanting to return multiple columns in a single row, you can get a little clever and instead of hard-coding the third argument, you can make use of the COLUMN() function, which dynamically returns whatever column you are in, so copying that formula across a row will increment the number by one each time.

For example, if the formula is in cell C2, and we want to return the 2rd column of our lookup range,
and then we want to copy the formula to cell D2 and return the 3th column of our lookup range,
and then we want to copy the formula to cell E2 and return the 5th column of our lookup range,
etc

We could use COLUMN()-1 as the third argument of our VLOOKUP function, as
if the formula is in column C, that would return 2 (as it is 3-1),
and if the formula is in column D that would return 3 (as it is 4-1),
and if the formula is in column E that would return 4 (as it is 5-1),
etc

If you run into any issues, the best thing to do would be to post your formula attempt here, and tell us what the issue is.

If you want to see more examples/tutorials, there are tons that can be found with a simple Google search or YouTube search (if you prefer videos).

 

[joeloveszoe]

Back up in post 6, your reply seemed to imply that you know how to use VLOOKUP (or are at least somewhat familiar with it). Is that not the case?
In post 7, I showed how you can structure these VLOOKUP formulas to suppress the errors, or return messages instead of the values.

Really, doing multiple VLOOKUPs is not any harder than doing one. You are just doing the same thing, over-and-over (as Excel formulas can only return values to the cells that they are in, and not to multiple cells at once).

If you are wanting to return multiple columns in a single row, you can get a little clever and instead of hard-coding the third argument, you can make use of the COLUMN() function, which dynamically returns whatever column you are in, so copying that formula across a row will increment the number by one each time.

For example, if the formula is in cell C2, and we want to return the 2rd column of our lookup range,
and then we want to copy the formula to cell D2 and return the 3th column of our lookup range,
and then we want to copy the formula to cell E2 and return the 5th column of our lookup range,
etc

We could use COLUMN()-1 as the third argument of our VLOOKUP function, as
if the formula is in column C, that would return 2 (as it is 3-1),
and if the formula is in column D that would return 3 (as it is 4-1),
and if the formula is in column E that would return 4 (as it is 5-1),
etc

If you run into any issues, the best thing to do would be to post your formula attempt here, and tell us what the issue is.

If you want to see more examples/tutorials, there are tons that can be found with a simple Google search or YouTube search (if you prefer videos).i have

 

[joeloveszoe]

thanks so much for your reply and help.
i have very little experience with v lookup. i'm being forced to learn as an alternate to access
i will try following your instructions and post if i get stuck

i am a visual learner and generally quick to pick things up, being able to work with someone with my worksheets would be great learning.
thanks again!

 

[Joe4]

i am a visual learner and generally quick to pick things up, being able to work with someone with my worksheets would be great learning.

I am a Visual Learner too. We do not offer "off-line" help services, though you can hire a Consultant if you want (these services are not provided by MrExcel, but they have some recommendations: Consulting Services)

MrExcel himself does a bunch of videos. A quick search brought back this one of interest: MrExcel's Learn Excel #484 - VLOOKUP vs MATCH
And if you go out to YouTube and type in "Excel VLOOKUP", you will gets tons of hits (no shortage of videos on this!).

And we can continue to help you through this thread, if you run into issues.

 

[joeloveszoe]

I am a Visual Learner too. We do not offer "off-line" help services, though you can hire a Consultant if you want (these services are not provided by MrExcel, but they have some recommendations: Consulting Services)

MrExcel himself does a bunch of videos. A quick search brought back this one of interest: MrExcel's Learn Excel #484 - VLOOKUP vs MATCH
And if you go out to YouTube and type in "Excel VLOOKUP", you will gets tons of hits (no shortage of videos on this!).

And we can continue to help you through this thread, if you run into issues.

thank you again so much!
really appreciative of you, your time and your help
have a happy day =)

 

[Joe4]

You are welcome.

As I mentioned, if you run into any issues trying to create the formula, just post your formula and question back to this thread.