Philippe has attended:
Excel Advanced course
Excel VBA Introduction course
MATCH
Good morning,
I want to be able to access information located on several spreadsheet.
For example, on spreadsheet Nr1, I will have a product code and in the same line, I will have a quantity.
On spreadsheet Nr2, I will have several product codes but I want to access the quantity (spreadsheet Nr1) based on teh product code.
How can I do that please?
Thank you.
Kind Regards.
RE: MATCH
Hi Philippe,
Thank you for the forum question.
You can use the combination Index Match to look up the quantity.
In a column on sheet 2 you can write:
=Index("name of sheet 1"!"the quantity range",Match("lookup product code","sheet 1 product code range,0))
Let's say that your sheet 1 is called "quantity", you have the product codes from A2 to A200 on sheet 1, and the quantity from C2 to C200, and you have the product code from B2 to B50 on sheet 2. The the formulas should look like this:
=Index('quantity'!$C$2:$C$200,Match(B2,'quantity'!$A$200))
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: MATCH
Hi Jens,
Thank you for your response.
However, I m afraid I am not able to perform this.
If I want to make things even simpler and find match information on one single worksheet.
I will have in A1 "CODE" with a list of codes underneath.
I will have in B1 "Quantity3 with the quantities for each code underneath.
If in E1, I have "CODE" and in E2, I have one of the codes appearing in the cells A2 to A6, how do I get the corresponding quantity in F2 please?
Thank you.
Kind Regards,
Philippe
RE: MATCH
Hi Philippe,
In F2 type =Index($B$2:$B$6,Match(E2,$A$2:$A$6,0))
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: MATCH
Thank you Jens.
That s what I thought.
However, when typiung the formula, I get a error code after typing E2 after MATCH.
I checked several times, the syntax seems to be correct…..
Could it be because I am using a French version of Excel and the commands are different?
The messqge I am getting :
"Are you trying to use a formula? If the first sign is =, then Excel thinks it is a formula. Yous hould use "first if you are not trying to use a formula.
Sorry to bother you with Something so trivial.
Kind Regards,
Philippe
RE: MATCH
Hi Philippe,
If you have a French version you will need to write this in F2.
=Index($B$2:$B$6,Equiv(E2,$A$2:$A$6,0))
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: MATCH
Hi Philippe,
I have done the index match in your file and you should receive the file shortly from our office.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: MATCH
Hi Philippe,
I will have to see the worksheet to help you or if it is not possible you will need to create an example worksheet.
If you could send me an example to:
Info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
