98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » MATCH
MATCH
Resolved · High Priority · Version 2016
Tue 27 Aug 2019, 09:02Delegate Philippe said...
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.
Thu 29 Aug 2019, 13:10Trainer Jens said...
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
Fri 30 Aug 2019, 09:30Delegate Philippe said...
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
Fri 30 Aug 2019, 13:09Trainer Jens said...
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
Fri 30 Aug 2019, 13:27Delegate Philippe said...
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
Fri 30 Aug 2019, 13:49Trainer Jens said...
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
Fri 30 Aug 2019, 15:39Trainer Jens said...
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
Fri 30 Aug 2019, 14:22Trainer Jens said...
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Selecting constant values onlyIf periodically you need to change all your values back to zero, but leave formulas, text and blank cells as they are select the entire worksheet, choose F5 function key, Special and then Constants and choose the appropriate sub-selections. To enter zero in all the selected cells type 0 and then press Ctrl+Enter. |
