Solved

MATCH FORMULA

Posted on 2014-10-08
10
185 Views
Last Modified: 2014-10-08
Hi,

In the attached wb i have some figures highlighted red in Column L, can someone insert match formula so that it picks up the numbers based on the region in Col K and the month in  L1

Many thanks
EE.xlsx
0
Comment
Question by:Seamus2626
  • 4
  • 3
  • 2
  • +1
10 Comments
 

Author Comment

by:Seamus2626
ID: 40368128
updated workbook
EE.xlsx
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 200 total points
ID: 40368142
For the ASP formula use:

=INDEX($B$1:$H$13,MATCH($L$1,$B$1:$B$13,0),MATCH($K3,$B$1:$H$1,0))

Copy down as required.

Thanks
Rob H
0
 
LVL 6

Assisted Solution

by:johnb25
johnb25 earned 150 total points
ID: 40368151
See Attached.

John
EE.xlsx
0
 
LVL 19

Assisted Solution

by:helpfinder
helpfinder earned 150 total points
ID: 40368160
or try HLOOKUP formula to find a mach
=HLOOKUP(K3,$B$1:$H$10,3,FALSE)

in this case it looks for value for February (based on 3 in the formula, where 3 is a row number, if you change to 2 if will return values for Janury, or 7 for June)
EE-1.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40368170
Using HLOOKUP you could also set the row number using MATCH:

=HLOOKUP($K3,$B$1:$H$13,MATCH($L$1,$B$1:$B$13,0),FALSE)

Likewise using VLOOKUP you could set the column using MATCH:

=VLOOKUP($L$1,$B$1:$H$13,MATCH($K3,$B$1:$H$1,0),FALSE)

Thanks
Rob H
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 19

Expert Comment

by:helpfinder
ID: 40368176
I have edited the formula I posted, so now you can just choose month from drop down menu (L1) and you will get the values for appropriate month - see attached file
EE-1.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40368193
@Helpfinder - what is the point of using additional helper columns when it can all be done with existing data?

Nice touch adding a the drop-down for month but this could be linked to column B rather than creating a new list.
The row can be determined by using MATCH on column B.

Thanks
Rob H
0
 
LVL 19

Expert Comment

by:helpfinder
ID: 40368212
@Rob Henson - the point is just to be more user friendly. if user picks the month from drop down menu it could eliminate typo errors since user can type "march" instead of "mar" and formula won´t work.

I am sure there are multiple options in excel how to achive the same result - depends on user which is most suitable for him.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40368251
Indeed, many ways to "skin a cat" as they say.

This poor cat has been well & truly skinned!
0
 

Author Closing Comment

by:Seamus2626
ID: 40368671
Thanks guys!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Freeze Panes Solution 6 28
Filling Blank Cells 14 19
Avoid Multiple Instances of Named Ranges in Excel 3 16
Vlookup formula error 15 11
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now