How to get the corresponding quantity?

[TropicalMagic]

Hi all,



I have two worksheets in the same workbook



The first screenshot is Worksheet A

The second screenshot is 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!

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to get the corresponding quantity?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[EFANYoutube]

Based solely on the above example this should work

=VLOOKUP(B10&C10&E10&F10&D10,Sheet2!F:K,COLUMN(INDIRECT(H10&"1")),0)

 

[Fluff]

@EFANYoutube the OP does not have concat.

 

[EFANYoutube]

@EFANYoutube the OP does not have concat.

Very good point, I have edited the reply.

 

[TropicalMagic]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to get the corresponding quantity?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies in advance as I have just arrived!

 

[TropicalMagic]

Based solely on the above example this should work

=VLOOKUP(B10&C10&E10&F10&D10,Sheet2!F:K,COLUMN(INDIRECT(H10&"1")),0)

Thanks for responding!

I found that I had to edit your code a little to get a working answer:

=VLOOKUP(CONCAT(B10,"-",C10,"-",E10,"-",F10,"-",D10),'Reference - Grading'!$F$4:$K$26,MATCH('Part 1 - Buying Template'!M10,'Reference - Grading'!$F$3:$K$3,0),FALSE)

Best regards!

 

[Fluff]

What version of Excel are you using? As 2016 does not have the CONCAT function.

 

[TropicalMagic]

What version of Excel are you using? As 2016 does not have the CONCAT function.

Apologies! I did not notice carefully my product is indeed Microsoft 365! I have updated my product accordingly!

 

[Fluff]

In that case you could use textjoin instead of concat, like

=VLOOKUP(TEXTJOIN("-",,B10:C10,E10:F10,D10),B!F4:K26,MATCH(I10,B!F3:K3,0),0)