CODEWITHSUNDEEP
excelexcel-formula
TropicalMagic
TropicalMagic

Reputation: 126

Excel - How to get the corresponding quantity?

I have 1 workbook with 2 worksheets.

enter image description here

enter image description here

The first screenshot shows Worksheet A, the second screenshot shows Worksheet B

I would like to match the corresponding quantity to the corresponding grade in Worksheet A based on the Columns "Line", "Sub-Line", "Category", "Occasion", "Sub-Cat" and "Grade" in Worksheet B.

Question: How to get the corresponding quantity?

Many thanks!

Upvotes: 0

Views: 195

Answers (4)

TropicalMagic
TropicalMagic

Reputation: 126

This worked for me!

=VLOOKUP(CONCAT(B10,"-",C10,"-",E10,"-",F10,"-",D10),'Worksheet2'!$F$4:$K$26,MATCH('Worksheet1'!M10,'Worksheet2'!$F$3:$K$3,0),FALSE)

Cheers

Upvotes: 0

P.b
P.b

Reputation: 11483

You can use INDEX and MATCH for this: =INDEX('Worksheet B'!$G$4:$K$26,MATCH(1,($B5='Worksheet B'!$A$4:$A$26)*($C5='Worksheet B'!$B$4:$B$26)*($E5='Worksheet B'!$C$4:$C$26)*($D5='Worksheet B'!$D$4:$D$26)*($F5='Worksheet B'!$E$4:$E$26),0),MATCH($I5,'Worksheet B'!$G$3:$K$3,0))

The formula needs to be entered with ctrl+shift+enter as it's an array formula.

It indexes the quantities in 'Worksheet B'!$G$4:$K$2 and returns the row number of where the value in Worksheet A of the line, sub-line, occasion, category, sub-cat matches the value in the resembling column in Worksheet B and the indexed column number of where the grade value in Worksheet A equals the header in Worksheet B'!$G$3:$K$3.

Upvotes: 1

Variatus
Variatus

Reputation: 14383

Since you already have a concatenation of the search criteria in SheetB!F:F you might use this formula if you have Excel 365 (much earlier versions won't have the TEXTJOIN function).

=VLOOKUP(J2, SheetB!$F$2:$K$1000, MATCH(I2,SheetB!$F$1:$K$1, 0), FALSE)

Witness that the MATCH function includes F1, which must not have any of the captions found in G1:K1. $K$1000 is an arbitrary row number intended to be larger than anything you need. For use in my own project, I would create a dynamic named range instead of SheetB!$F$2:$K$1000.

Upvotes: 2

mark fitzpatrick
mark fitzpatrick

Reputation: 3320

This is a little heavy and perhaps someone can make an easier approach, but let's give it a go. NB: This requires Excel 2019 or 365.

You can put this into your QTY column in WORKSHEET A:

=SUM(TRANSPOSE(MMULT(SIGN(SEQUENCE(1,5)),TRANSPOSE(--(B5:F5=WorksheetB!$A$3:$E$14)))=5)*INDEX(WorksheetB!$G$3:$K$14,,MATCH(G5,WorksheetB!$G$2:$K$2,0))

That's hard to read, so here it is again with formatting (it works with formatting all the same):

=SUM( TRANSPOSE( MMULT( SIGN( SEQUENCE( 1, 5) ),
                        TRANSPOSE(--(B5:F5=WorksheetB!$A$3:$E$14) ) ) = 5 )
      * INDEX( WorksheetB!$G$3:$K$14,,MATCH( G5, WorksheetB!$G$2:$K$2,0) ) )

Seems there would be an easier way, but it does not come to me at the moment.

As a side note - what you are doing looks very much like something that could be done in Power Query. It would make quick work of doing such match-ups and aggregations and if there is a lot more that you want to do with these data, it would support that as well.

Upvotes: 1

Related Questions