Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

match

ResolvedVersion 2016

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

I am still getting the same error message Jens...

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 Jens,
Thank you for your help.
So it was just a case of using ";" instead of"," ?
I will try it of my original spreadsheets and will revert if I have a problem.
Thank you so much again.
Kind Regards,
Philippe

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

Excel tip:

Page Break Preview in Excel 2010 (Hint/tip)

If you select View then Workbook Views then Page Break Preview, you will be able to view how your Excel spreadsheet will be split across multiple pages when printed. Even better, you can also drag a page break to a new place. Excel will then scale down your entire worksheet to fit the information you want on the pages you want.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.16 secs.