More robust formula to extract data

**ell_** · 03-20-2018, 04:25 AM

Hi, all

I previously tried to apply HLOOKUP formula to capture the data based on month but it is really time consuming. Hence, I'm seeking for help as I'm new to functions.

I have a sheet named "Raw" which stores all data and another sheet named "Summary". What I have to do is that, I have to extract data accordingly, based on the month in cell D1. The data has to change just like how when I applied the HLOOKUP formula in cell D2. Is it possible to do a more robust formula where I can simply drag it to the right and to the bottom?

Hereby I attached an example of my file.

I believe I have to use Index and Match after searching online but I'm not really sure either. Can anyone enlighten me?

Thanks in advance!

**AliGW** · 03-20-2018, 04:40 AM

What do you mean by 'time-consuming' and 'more robust'?

**ell_** · 03-20-2018, 04:51 AM

When I apply HLOOKUP, I have to change the index num row accordingly for each cell. And more robust means that, I hope the formula can be easily dragged to the right without changing the formula in every cell, unlike what I did with HLOOKUP.

**Glenn Kennedy** · 03-20-2018, 05:40 AM

Please manually enter expected results for Branch C and repost your sheet.

**Glenn Kennedy** · 03-20-2018, 06:16 AM

No reply....

Your data layout is a bit of a mess!! Blank rows in a data table (e.g. Summary B3 & B4, etc) greatly increase the complexity. Also,you are flying up & down the date column, within each branch, rather randomly. So. No SIMPLE solution, I think.

D2, copied down:

=INDEX(Raw!$A$2:$P$49,MATCH(INDEX($B$2:$B$10,1+3*INT((ROWS($1:1)-1)/3)),Raw!$C$2:$C$49,0)+13+MOD((ROWS($1:1)-1),3),MATCH(Summary!$D$1,Raw!$A$1:$P$1,0))

E2:
Blank

F2
=INDEX(Raw!$A$2:$P$49,MATCH(INDEX($B$2:$B$10,1+3*INT((ROWS($1:2)-1)/3)),Raw!$C$2:$C$49,0)+(COLUMNS($E:E)-1)+MOD((ROWS($1:2)-1),3),MATCH(Summary!$D$1,Raw!$A$1:$P$1,0))

G2:
=INDEX(Raw!$A$2:$P$49,MATCH(INDEX($B$2:$B$10,1+3*INT((ROWS($1:3)-1)/3)),Raw!$C$2:$C$49,0)+6+(COLUMNS($E:E)-1)+MOD((ROWS($1:3)-1),3),MATCH(Summary!$D$1,Raw!$A$1:$P$1,0))

Select E2,F2 and G2 TOGETHER. Drag across and down.

**ell_** · 03-20-2018, 06:43 AM

Hi, Glenn

I was outside just now. Hurried back to my laptop to send the updated file to you. I have tried the formula and they work well, thank you so much despite the messiness and the confusion! Definitely a life savior!

P/s: the template was made by my boss, sorry about that

**Glenn Kennedy** · 03-20-2018, 06:47 AM

Data layout courses should be compulsory for bosses....

**ell_** · 03-20-2018, 06:49 AM

Originally Posted by Glenn Kennedy

Data layout courses should be compulsory for bosses....

I second that!

More robust formula to extract data

LinkBack

Thread Tools

Rate This Thread

Display

More robust formula to extract data

Re: More robust formula to extract data

Re: More robust formula to extract data

Re: More robust formula to extract data

Re: More robust formula to extract data

Re: More robust formula to extract data

Re: More robust formula to extract data

Re: More robust formula to extract data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Overwrite old data and filter using 2 parameters

Filter primary pivottable Data Model with parameters from another table

[SOLVED] Sum column data based on parameters in row

Dynamically change data based on parameters

[SOLVED] Locating a row in a table of data based on specified parameters

How do I transfer User-Defined filter parameters to then filter another column?

[SOLVED] How to summarize data based on specific parameters

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions