How to Combine Sumproduct with Index Match

[andyka]

Hi
I have following Table:

A B C D E F
Yr21 Yr22 Yr23 Yr24
yr21 £10,000
yr22 £8,000
yr23 £12,000

I am looking for following:
A B C D E F
Yr21 Yr22 Yr23 Yr24
yr21 £10,000 21 - £10,000
yr22 £8,000 22-£8,000
yr23 £12,000 23-£12,000

Currently I have two separate formulas but need help to combine both.

Formula to extract 21 from text string: =SUMPRODUCT(MID(0&G5, LARGE(INDEX(ISNUMBER(--MID(G5, ROW(INDIRECT("1:"&LEN(G5))), 1)) * ROW(INDIRECT("1:"&LEN(G5))), 0), ROW(INDIRECT("1:"&LEN(G5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(G5)))/10)

Formula to Index Match: =IFNA(INDEX($L$5:$L$10,MATCH(M$4,$K$5:$K$10,0),MATCH($K5,M$4,0)),"")

Please help

Thank you

 

[Fluff]

Those two formula make no sense with the data you have shown, can you post some sample data including the correct formulae.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[andyka]

Hi Fluff
Thank you ever so much for considering my Thread

 

[Fluff]

That does help either as I cannot see what rows/columns everything is in & it also means I have to type it all out. Please see post#2 about how to post data (not an image)

 

[andyka]

My Apologies Fluff. Its my first time. I am new to MrExcel site.

Extract number from text string.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
2
3					=SUMPRODUCT(MID(0&G5, LARGE(INDEX(ISNUMBER(--MID(G5, ROW(INDIRECT("1:"&LEN(G5))), 1)) * ROW(INDIRECT("1:"&LEN(G5))), 0), ROW(INDIRECT("1:"&LEN(G5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(G5)))/10)
4					yr21
5				yr21	21
6										yr21	yr22	yr23	yr24	yr25	yr26
7								yr21	£10,000	10000							=IFNA(INDEX($L$7:$L$12,MATCH(M$6,$K$7:$K$12,0),MATCH($K7,M$6,0)),"")
8								yr22	£8,000		8000
9								yr23	£12,000			12000
10								yr24	£6,000				6000
11								yr25	£15,000					15000
12								yr26	£22,000						22000
13
14
15									I am Looking for the following: Combine both above formula to achieve values below
16										yr21	yr22	yr23	yr24	yr25	yr26
17								yr21	£10,000	21-£10,000
18								yr22	£8,000		22-£8,000
19								yr23	£12,000			23-£12,000
20								yr24	£6,000				24-£6,000
21								yr25	£15,000					25-£15,000
22								yr26	£22,000						26-£22,000
23
24
25
26
27
28
Sheet1

Cell Formulas
Range	Formula
H5	H5	=SUMPRODUCT(MID(0&G5, LARGE(INDEX(ISNUMBER(--MID(G5, ROW(INDIRECT("1:"&LEN(G5))), 1)) * ROW(INDIRECT("1:"&LEN(G5))), 0), ROW(INDIRECT("1:"&LEN(G5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(G5)))/10)
M7:R12	M7	=IFNA(INDEX($L$7:$L$12,MATCH(M$6,$K$7:$K$12,0),MATCH($K7,M$6,0)),"")

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R
4		yr21
5	yr21	21
6							yr21	yr22	yr23	yr24	yr25	yr26
7					yr21	10000	21-£10,000
8					yr22	8000		22-£8,000
9					yr23	12000			23-£12,000
10					yr24	6000				24-£6,000
11					yr25	15000					25-£15,000
12					yr26	22000						26-£22,000
Main

Cell Formulas
Range	Formula
H5	H5	=SUBSTITUTE(G5,"yr","")+0
M7:R12	M7	=IFNA(SUBSTITUTE(M$6,"yr","")&"-"&TEXT(INDEX($L$7:$L$12,MATCH(M$6,$K$7:$K$12,0),MATCH($K7,M$6,0)),"£#,##"),"")

 

[andyka]

OMG! Fluff.
You're a Star, You're a Champion. Thank you! Thank you!...... So much. Problem Solved.

 

[Fluff]

You're welcome & thanks for the feedback.