Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MATCH FORMULA

Posted on 2014-10-08
10
189 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

860 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