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!

 

[mart37]

You can use Power Query or Power Pivot to combine tables like ACCESS

 

[joeloveszoe]

You can use Power Query or Power Pivot to combine tables like ACCESS

thanks for the response - looking for an alternate to access

 

[Joe4]

Quite frankly,

thanks for the response - looking for an alternate to access

Power Pivot is a part of Excel, not Access.
So it would be an alternative to Access.

Are you looking to return every single field, or just one or two?
If you are looking to return every single field, this sort of thing is typically easier in Acces than in Excel (just wanted to note that it will probably harder, not easier, than Access).
That is because what you have is a relational database model, and Access is a relational database program (built for that kind of stuff) and Excel is not really designed for relational database stuff (though people often use it for such purposes).

 

[Joe4]

Look at the three stickys as the top of the "Power BI" forum to learn more about Power Pivot and the Power products: Power BI

 

[joeloveszoe]

Quite frankly,

Power Pivot is a part of Excel, not Access.
So it would be an alternative to Access.

Are you looking to return every single field, or just one or two?
If you are looking to return every single field, this sort of thing is typically easier in Acces than in Excel (just wanted to note that it will probably harder, not easier, than Access).
That is because what you have is a relational database model, and Access is a relational database program (built for that kind of stuff) and Excel is not really designed for relational database stuff (though people often use it for such purposes).

thanks - i misread earlier!
i use excel fo mac
i have been using parallels just to run access - but now with the parallel issue with the M1 chip, i'm looking for a new solution
generally just looking to match/combine skus from 2 spreadsheets and pull in new costs, unit of measure, selling quantity .... i know i cna do that with a vlookup

i am also looking for a way to identify skus on one spreadsheet that are not on the other

 

[Joe4]

i use excel fo mac

Yes, that is a very important fact that is good to know, as Power BI is only available for Windows, not Mac.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

generally just looking to match/combine skus from 2 spreadsheets and pull in new costs, unit of measure, selling quantity .... i know i cna do that with a vlookup

i am also looking for a way to identify skus on one spreadsheet that are not on the other

Yes, you can do this with multiple VLOOKUPs. Note that you would need to do two sets of VLOOKUPs, one from list A looking into list B, and vice versa.

You can return the matching value or "Not found" message by wrapping your VLOOKUP in an IFERROR function, i.e.
=IFERROR(VLOOKUP(...),"Not found")

Or, if you want to return "Found/not found", you would need to use a structure something like this:
=IF(ISERROR(VLOOKUP(...)),"Not found","Found")

 

[joeloveszoe]

Yes, that is a very important fact that is good to know, as Power BI is only available for Windows, not Mac.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Yes, you can do this with multiple VLOOKUPs. Note that you would need to do two sets of VLOOKUPs, one from list A looking into list B, and vice versa.

You can return the matching value or "Not found" message by wrapping your VLOOKUP in an IFERROR function, i.e.
=IFERROR(VLOOKUP(...),"Not found")

Or, if you want to return "Found/not found", you would need to use a structure something like this:
=IF(ISERROR(VLOOKUP(...)),"Not found","Found")

i updated my account to mac 365 - thanks!

and i will check out and try your suggested formula/help!
i am appreciative

 

[Joe4]

You are welcome, and thank you for updating your account details!

 

[joeloveszoe]

Yes, that is a very important fact that is good to know, as Power BI is only available for Windows, not Mac.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Yes, you can do this with multiple VLOOKUPs. Note that you would need to do two sets of VLOOKUPs, one from list A looking into list B, and vice versa.

You can return the matching value or "Not found" message by wrapping your VLOOKUP in an IFERROR function, i.e.
=IFERROR(VLOOKUP(...),"Not found")

Or, if you want to return "Found/not found", you would need to use a structure something like this:
=IF(ISERROR(VLOOKUP(...)),"Not found","Found")

hi again
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?
(am i allowed to ask that here?)
thanks
j