I have two spreadsheets: A sheet contains sku no, unit price and other details of each sku no and B sheet contains sku nos and order quantities of last month. I need to get the details of skus from sheet A into matching skus in sheet B for further analysis. Can someone advise me how to accomplish this task. Thanks!
Call for matching data
- Thread starter ScottXe
- Start date
Hi Paul,
Following your advice, I may be getting there but encountered some catches seeking your further advice. I attached a sample file to illustrate the situation. I successfully called the data from vlookup table but not the last row. Secondly, I repeated the method for the column "Power", how can I set the table array of data in vlookup table since there is a column in between. Alternatively how can we pull more than one column from vlookup table. Thanks!
Following your advice, I may be getting there but encountered some catches seeking your further advice. I attached a sample file to illustrate the situation. I successfully called the data from vlookup table but not the last row. Secondly, I repeated the method for the column "Power", how can I set the table array of data in vlookup table since there is a column in between. Alternatively how can we pull more than one column from vlookup table. Thanks!
Vlookup is fine if your data is laid out right to left and sorted. Otherwise the results can be incorrect. There is a comprehensive of the pro's and con's In excljet.net - google vlookup caution sort and you'll find it.
An alternative is to use the so-called Index Match. This is a combination of two functions nested together to produce the result. The basic configuration of each formula is:
=INDEX([array], [row_number], [column_number])
=MATCH([lookup_value], [lookup_array], [match_type])
You combine then to do your lookup as follows:
=INDEX([array], match (..........), [column_number]) or
=INDEX([array], [row_number], match (..........))
Note that "array" simply refers to a column or range of data.
There's plenty of information on the web, so I'll leave it to you to google a spreadsheet forum to get specific help.
An alternative is to use the so-called Index Match. This is a combination of two functions nested together to produce the result. The basic configuration of each formula is:
=INDEX([array], [row_number], [column_number])
=MATCH([lookup_value], [lookup_array], [match_type])
You combine then to do your lookup as follows:
=INDEX([array], match (..........), [column_number]) or
=INDEX([array], [row_number], match (..........))
Note that "array" simply refers to a column or range of data.
There's plenty of information on the web, so I'll leave it to you to google a spreadsheet forum to get specific help.
Let me try to see any benefit with this alternative! Thanks for your sharing.
