Solved

# MATCH FORMULA

Posted on 2014-10-08
188 Views
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
Question by:Seamus2626
• 4
• 3
• 2
• +1

Author Comment

ID: 40368128
updated workbook
EE.xlsx
0

LVL 32

Accepted Solution

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

johnb25 earned 150 total points
ID: 40368151
See Attached.

John
EE.xlsx
0

LVL 19

Assisted Solution

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

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

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

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

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

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

This poor cat has been well & truly skinned!
0

Author Closing Comment

ID: 40368671
Thanks guys!
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity anâ€¦
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 use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.