Solved

MATCH FORMULA

Posted on 2014-10-08
10
180 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
Comment Utility
updated workbook
EE.xlsx
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 200 total points
Comment Utility
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
Comment Utility
See Attached.

John
EE.xlsx
0
 
LVL 19

Assisted Solution

by:helpfinder
helpfinder earned 150 total points
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 19

Expert Comment

by:helpfinder
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
@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
Comment Utility
@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 31

Expert Comment

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

This poor cat has been well & truly skinned!
0
 

Author Closing Comment

by:Seamus2626
Comment Utility
Thanks guys!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

15 Experts available now in Live!

Get 1:1 Help Now