INDEX MATCH multiple values in same row

[surkdidat]

I need to match A2 and D2 (in a sheet called "5.12") - against Sheet1, (A2 against Column B, and D against Column F)

This will go down for several hundred lines.

I appreciate the easy solution would be to add a column combing the two and then matching that, but the sheet directly gets pulled into an Access database so I cannot fiddle with the format

So in effect (in real terms) I will have a Category Number (for example 12345) which will have 20 or so lines of data in the Master Sheet, one for each specific, (Colour, Weight, Size etc)

If I use Index Match one one value it obviously only picks up the first match. I have tried to do INDEX MATCH MATCH on 2 columns, on 2 values in the same row, but it does not seem to like it.

Any help appreciated!

(For reference I have tried

=INDEX(RawData!I:I,MATCH(A2,RawData!B:B,0),MATCH(D2,RawData!F:F,0))

SHEET 5.12	Column A - Batch	Column B - Cat_Number	Column D - Cat_Detail	Column E (Result of INDEX MATCH)
Row 1	1000	12345	Colour	Blue
Row 2	1000	12345	Weight	1.5
Row 3	1000	12345	Shape	Square
Row 4	1000	23456	Colour	White
Row 5	1000	23456	Weight	2.0
Row 6	1000	23456	Shape	Round

RawData	Column A	Column B - Category No	Column F - Cat Details	Column I - (Data that needs to be pulled from Index Match)
Row 1		12345	Colour	Blue
Row 2		12345	Shape	Square
Row 3		34564	Colour	Purple
Row 4		12345	Weight	1.5
Row 5		23456	Colour	White
Row 6		23456	Weight	2.0
Row 7		34564	Weight	4.0
Row 8		23456	Shape	Round
Row 9		34564	Shape	Triangle

 

[Fluff]

(A2 against Column B, and D against Column F)

From the dat you have posted it looks as though that should read B2 against col B, not A2, which is correct?

 

[surkdidat]

From the dat you have posted it looks as though that should read B2 against col B, not A2, which is correct?

Apologies - yes thats correct

 

[Fluff]

Apologies - yes thats correct

In that case try Marcelo's formula, he beat me to it.

 

[Marcelo Branco]

Try

E2 copied down
=INDEX(RawData!$I$2:$I$100,AGGREGATE(15,6,(ROW(RawData!$I$2:$I$100)-ROW(RawData!$I$2)+1)/((RawData!$B$2:$B$100=B2)*(RawData!$F$2:$F$100=D2)),1))

M.

 

[Marcelo Branco]

In that case try Marcelo's formula, he beat me to it.

I adjusted sheet name...

M.

 

[surkdidat]

Hi Marcelo

Thank you for this, it works perfectly, however, I think due to the size of my data, what i hoped would work, won't as it is taking forever to calculate. Perhaps I should have said this from the offset!

The "RawData" is from Row 2 to Row 404231, and stretches to Column M (I have dragged the formula in the sheet to cover 5 columns in the sheet I want the data populated. The sheet has approximately 19,500 lines, and there are a further 12 of these, plus another 4 with around 60K rows on.

Would a macro be a better idea that when I want an update, I click on a button and it updates what I need? (Rows G:K in my sheets, majority being 19500 rows, the most having 62001 rows)

Sorry, to be a pain, I did not take into account the sheer amount of formulas that would be needed and did not think of the calculations involved slowing everything down!)

I adjusted sheet name...

M.

 

[Marcelo Branco]

Wow you have a pretty big spreadsheet...

I didn't quite understand what the data in the RawData spreadsheet looks like. And it was also unclear why you need the formula in 5 columns (???) - seeing your initial post it looked like it was only necessary to extract the result for column E

Maybe if you describe your scenario better someone can help you with a macro.

M.

 

[Fluff]

Are you trying to get the data from columns I:M & put that into cols E:I?
Also is there any data already in the destination cells that needs to remain?

 

[surkdidat]

Hi Fluff/Marcelo

Thanks for your help/queries.

Yes, I probably should have explained it a bit better with the amount of data, and the fact I was going to adapt the formula to go over multiple columns - I thought having the formula provided, I could then just adapt (which I did successfully) over the sheets but alas no!

So the above examples still stand, but the results f the "Index Match" in "Sheet 5.12", are for multiple columns from G to K. (E was given on the example as a working example only). The fields in the Raw Data Master Data sheet still stand, and the data being matched is in Columns B for the "Category Number" and Column F for the "Category Details".

On myspreadsheet there are tabs ranging from 5.00 through to 5.15. (The above example just used one of these 5.12)

In the RawData master sheet, the data goes down 400K odd rows.

The data that will need to be pulled from the Master Raw Data Sheet is in Columns I through to M.

The data must remain in the main Raw Data Sheet

The data in the tabs 5.00 to 5.15 (that need to be populated G to K) from I to M in the Raw Data, are all presently blank. The data in the main tab will remain the same, so the macro could either wipe these cells blank every time and repopulate upon running of the Macro, or only look for cells that are blank in the destination sheets and fill them in.

I should also include that the name of the sheets the data needs to go to are in the Raw Data spreadsheet in Column A

So....

Raw Data (Main Sheet)
Column A - Tab Name (5.00 ,5.01, 5.02, 5.03, 504 ...... 5.15)
Column B - Category Number
Column F - Category Details
Column I - Data set 1 that needs to be pulled
Column J - Data set 2 that needs to be pulled
Column K - Data set 3 that needs to be pulled
Column L - Data set 4 that needs to be pulled
Column M - Data set 5 that needs to be pulled

Destination Tabs (numbered 5.00, 5.01, 5.02 ..... 5.15)
Column A - Category Number
Columns B/C - Prepopulated Static Data (Must remain in sheet)
Column D - Category Details
Column E/F - Prepopulated Static Data (Must remain in sheet)
Column G - Initially Blank - but needs to pull data from Raw Data Column I
Column H - Initially Blank - but needs to pull data from Raw Data Column J
Column I - Initially Blank - but needs to pull data from Raw Data Column K
Column J - Initially Blank - but needs to pull data from Raw Data Column L
Column K - Initially Blank - but needs to pull data from Raw Data Column M


There maybe blank cells in rows where data is pulled - especially for the last two columns which rarely will have any data.

The main data may not be populated in order, so would need the macro to run to the last row every time, and not stop on the first row/cell that is empty.

I hope that all helps, and can explain why I requested a formula to start as I thought that would be the far more simple solution I could adapt for my needs without a lengthy explation (as above) that would be required for the Macro!!!
I hope that makes sense.