Solved

MATCH FORMULA

Posted on 2014-10-08
10
191 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
[X]
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
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

728 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