Need some help with Index/match formula

[Roma1r]

Results in sheet "JBS" B4. The data is in "workings" sheet and we need to match / Lookup the following. terminal (B1), Month (B2), Throughput column (B3) - by material (A4:A15)
I'm attaching excel workbook

Many thanks in advance

[xlnitwit]

Hi,

Your workbook could really use some lookup tables and helper columns. You could use a formula like this in B4
=SUMIFS(INDEX(Workings!$E:$I,,MATCH(JBS!$B$2&JBS!B$3,Workings!$E$1:$I$1&Workings!$E$2:$I$2,0)),Workings!$B:$B,$B$1,Workings!$D:$D,$A4)
which needs to be array entered using Ctrl+Shift+Enter and then filled down and across. If you have more months, you will need to expand the ranges and you also need to be sure that on the Workings tab the month/year is repeated for every column.

[Roma1r]

Hi Don, thank you. Getting a #VALUE when dropping the formula in B4:-( what do I do wrong?
Also you comment "Your workbook could really use some lookup tables and helper columns", I agree it would be better to do some form of lookup but have no idea of how to do this hence the straight "= copy". would appreciate you provide some guidance pls?

Thank you

Ralph

[FDibbins]

Romar, welkom aan die forum, hoe gaan dit daar in die Kaap?
SA ex-pat PE old boy from klerksdorp, now living in USA (son works in CT)

Maybe Im missing something here, but your references dont seem to match your file?

terminal (B1), Month (B2), Throughput column (B3) - by material (A4:A15)

Where (exactly) is the data you are referencing, and where (exactly) do you want the answer/s? If necessary, provide some sample answers, too.

As far as setting up a small lookup table, it would look something like this....

	A	B
1	95 Octane ULP	Unleaded - 95
2	95 Octane ULP with Techron	Unleaded - 95
3	95 Octane LRP with Techron	Lead Replacement - 95
4	95 Octane LRP	Lead Replacement - 95
5	Marine Distillate Fuel	Marine Fuel

Where the data is in the 1st column, and what you want returned, is in the 2nd column
Then a simple VLOOKUP will pull back what you want.

[Roma1r]

Hello Ford - small world, right? Lekker innie Kaap net nie baie water nie!
okay let me try again as the formula provided by Don looks fine for someone who is not an expert at all
let me try to be more simple - and the data is in the "working" sheet

In JBS B4 we would like to see the Throughput volume 5 479 346 against material Marine fuel for Terminal JBS - in other words the sum of all volumes for material 1 but in this case it is only a one liner against material 1 Marine fuel
Marine Fuel 5 479 346
Marine Fuel
Marine Fuel


Similarly In JBS C4 we would like to see a sum of the loss/Gains which is in column F in the workings sheet
Marine Fuel 21 572
Marine Fuel -20 226

Fuel Oil 28 164
Fuel Oil -40 798

Hope this helps

Cheers - Ralph vannie Kaap

[FDibbins]

Yes, you guys are having a rough time with the drought there

I put this in B11 and copied down and across...
=SUMIFS(Workings!E:E,Workings!$D:$D,JBS!$A11,Workings!$B:$B,JBS!$B$1)

[Roma1r]

Not getting a result:-( - Please can you attach the excel file or I can send mine again?

also pls remember the formula must also look at the month and not the Terminal name only

Thank you

[FDibbins]

Yes, I get that it needs to look at the month, but I figured we would start simple (hard-code the month column) and work up from there

When you say its not working, wrong answer? no answer? something else?

[Roma1r]

Sorry what I meant by not getting a result - it returned a 0 value
okay I understand, lets start simple and work our way through to Feb as well

Tx
Ralph

[FDibbins]

THis uis what I got, using that formula, conpared to your examples...

	A	B	C
3		Throughput	Loss/Gains
4	Unleaded - 95
5	Unleaded - 93	1,000,000	105
6	Lead Replacement - 95
7	Lead Replacement - 93
8	Diesel 50
9	Diesel 500
10	IK
11	Marine Fuel	(5,479,346)	1,345
12	Fuel Oil	-	(12,634)
13	HFO
14	Jet

[Roma1r]

sorry have to run off to meeting quick - attaching my workbook
talk a little later

[FDibbins]

Bed time here too, will pick up again tomorrow

[FDibbins]

OK just had a quick look at your 2nd file - you have a space after JBS in B1

[Roma1r]

Thank you - got it
Can we now try and look for the month as well?

Tx Ralph

[FDibbins]

Try this for K10...
=SUMIFS(INDEX(Workings!$E$3:$I$1000,0,MATCH($B$2,Workings!$E$1:$I$1,0)+COLUMNS($A$1:A$1)-1),Workings!$D$3:$D$1000,$A11,Workings!$B$3:$B$1000,$B$1)
copy down and across as needed.

[Roma1r]

It works! Thanking you
I must just still work out how best to lookup from sheet table into workings sheet:-(

[FDibbins]

What do you mean by that?

[Roma1r]

it is okay thanks Ford

[FDibbins]

Jy is welkom