Combine MATCH and INDEX

[SubwAy]

Hi guys,

I've spent quite a bit of time on this now, browsing forums, reading tutorials and it seems that I simply don't get how to combine MATCH and INDEX for the file I'm working on. I'm pretty sure it is the right way to do what I want but if any of you has another suggestion, I'll be happy to give it a go :-)

I have attached a mock version of what I'm trying to do. The format of the file, the way the data is presented cannot be changed unfortunately. Basically, the consumption tab is a tab I have to fill out manually and the reporting one is the one I'm trying to automate. I'm hoping to display the correct consumption figure in column E based on the values entered in A,B,C and D.

You will see that I have concatenated (not sure I can make a verb out of this) the values to do a lookup on "consumption column A" but I would like to also cross reference the month. The intent is that if for whatever reason I change the month in B6 in "reporting", the value will change and pick up the right figure from "consumption".

This very bad explanation only works if you download the file :-)

Thanks in advance for reading this thread and hopefully helping me!

Sub

Template Spend.xls

[Andrew-R]

In cell E2 how about:

=INDEX(Consumption!$E$3:$G$7, MATCH(LEFT(C2,3) & D2 & A2,Consumption!$A$3:$A$7,0),MATCH(B2,Consumption!$E$2:$G$2,0))

[npamcpp]

First, the merged cells will cause problems, sooner or later. Do not use merged cells. There is not enough aspirin to ease the headaches they cause.

Second, on the consumption sheet, the month value is "March" but on the Reporting sheet it is "Mar". These details matter, so please clean your data.

After you have done that, you can use this in cell E2 of the Reporting sheet and copy down

=VLOOKUP(LEFT(C2,3)&D2&A2,Consumption!$A$1:$G$7,MATCH(Reporting!B2,Consumption!$A$2:$G$2,0),FALSE)

[TMS]

Reporting!E2: =INDEX(Consumption!$E$3:$G$7,MATCH(LEFT($C2,3)&$D2&$A2,Consumption!$A$3:$A$7),MATCH($B2,Consumption!$E$2:$G$2,0))


Regards, TMS

[SubwAy]

This was amazingly fast and worked amazingly well. Makes me feel like an idiot for spending two days on this...

I'll adapt the formula to my model and confirm that it works before I mark the thread as resolved. Thank you very much for this, really.

Just wish I understood the formula exactly so I could replicate and adapt it in the future.

Sub

[TMS]

You are indexing a two dimensional array. The first parameter is for the row, and the second is for the column.

So, you build a lookup value using "&" (or concatenate) and MATCH that against the entries in column A. You then match the month against the month headers. Where they cross, you get your result. Not that you need to be consistent about the way that you write the month header and the month values. The 0 parameter for the MATCH says you want an exact match.

Regards, TMS

[npamcpp]

might help if you could name the post that you're referring to. Then the respective helper could explain...

[TMS]

Can I ask why you use only the leftmost three characters of the facility? That pretty much renders it redundant in this example.

Regards, TMS

[TMS]

@npamcpp: I've explained how my formula works, which is essentially the same as Andrew's. If you explain your formula, he has everything he needs.

The OP did ask for an INDEX/MATCH solution/explanation as the thread title.

Regards, TMS

[Andrew-R]

If it helps I'd delete column A in the Consumption sheet and do the concatenate in the lookup formula, like this:

=INDEX(Consumption!$D$3:$F$7, MATCH(C2 & D2 & A2,INDEX(Consumption!$B$3:$B$7 &Consumption!$C$3:$C$7 & Consumption!$A$3:$A$7,0),0),MATCH(B2,Consumption!$D$2:$F$2,0))

[npamcpp]

The OP did ask for an INDEX/MATCH solution/explanation as the thread title.

Sometimes another approach will deliver equal or better value.

If you explain your formula, he has everything he needs.

Here goes:

=VLOOKUP(LEFT(C2,3)&D2&A2,Consumption!$A$1:$G$7,MATCH(Reporting!B2,Consumption!$A$2:$G$2,0),FALSE)
LEFT(C2,3)&D2&A2 -- the search string
Consumption!$A$1:$G$7 -- the lookup table
MATCH(Reporting!B2,Consumption!$A$2:$G$2,0) -- the column where the return value is found. The Match formula will find the position of the value of cell B2 in the cells A2 to G2. It will return a number.

[npamcpp]

The OP did ask for an INDEX/MATCH solution/explanation as the thread title.

Sometimes another approach will deliver equal or better value.

If you explain your formula, he has everything he needs.

Here goes:

=VLOOKUP(LEFT(C2,3)&D2&A2,Consumption!$A$1:$G$7,MATCH(Reporting!B2,Consumption!$A$2:$G$2,0),FALSE)
LEFT(C2,3)&D2&A2 -- the search string
Consumption!$A$1:$G$7 -- the lookup table
MATCH(Reporting!B2,Consumption!$A$2:$G$2,0) -- the column where the return value is found. The Match formula will find the position of the value of cell B2 in the cells A2 to G2. It will return a number.

It goes without saying that the data needs to be cleaned for either an Index/Match or a Vlookup to work, i.e. "March" and "Mar" will not fly in either suggestion.

[TMS]

Sometimes another approach will deliver equal or better value.

Absolutely

[SubwAy]

Thanks to all of you guys!!!

Took me a bit of time but I think I understood all the formulas you gave me :-)

TO respond to TMShucks, the facilities had a similar name in my mock example only, not in my real model.

Thanks again for your amazingly quick and accurate help!

Sub