INDEX MATCH multiple values in same row

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
579
Office Version
  1. 2016
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
Excel Formula:
=INDEX(RawData!I:I,MATCH(A2,RawData!B:B,0),MATCH(D2,RawData!F:F,0))

SHEET 5.12Column A - BatchColumn B - Cat_NumberColumn D - Cat_DetailColumn E (Result of INDEX MATCH)
Row 1100012345ColourBlue
Row 2100012345Weight1.5
Row 3100012345ShapeSquare
Row 4100023456ColourWhite
Row 5100023456Weight2.0
Row 6100023456ShapeRound


RawData Column AColumn B - Category NoColumn F - Cat DetailsColumn I - (Data that needs to be pulled from Index Match)
Row 112345ColourBlue
Row 212345ShapeSquare
Row 334564ColourPurple
Row 412345Weight1.5
Row 523456ColourWhite
Row 623456Weight2.0
Row 734564Weight4.0
Row 823456ShapeRound
Row 934564ShapeTriangle
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
(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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top