Solved

MATCH FORMULA

Posted on 2014-10-08
10
190 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 33

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 33

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
 
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 33

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 33

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

733 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