Lookup formula returning multiple Columns based on 2 criteria

[SteynBS]

Hi Guys,

I think I am just missing something with my formula.

In table 1 (sheet3) I have a table with my code in Column B and different sizes with its values from Column D through H

In Table 3 (Sheet1) I have a table where I need to match the code and the size to return the value from that size column

So if I have a code in table 3 with a small size, I need it to return the value from the corresponding code in Table1 but the value in the Small column, if the same code has a Large size I need it to return the value from the Large column

The formulas I tried to use:
=INDEX(Table1[[XS]:[XL]],MATCH(1,(Table1[[#All],[Article]]=[@Article])*(Table1[[#Headers],[XS]:[XL]]=[@Size]),0))
=XLOOKUP(1,([@Article]=Table1[Article])*([@Size]=Table1[[#All],[XS]:[XL]]),Table1[[XS]:[XL]])


Any suggestions or help, please?

 

[Legacy 143009]

Have you tried like this?

=INDEX(Table1[#All],MATCH([@Article],Table1[[#All],[Article]],0),MATCH([@Size],Table1[#Headers],0))

 

[Skovgaard]

Hi Guys,

I think I am just missing something with my formula.

In table 1 (sheet3) I have a table with my code in Column B and different sizes with its values from Column D through H

View attachment 75558
In Table 3 (Sheet1) I have a table where I need to match the code and the size to return the value from that size column

View attachment 75559

So if I have a code in table 3 with a small size, I need it to return the value from the corresponding code in Table1 but the value in the Small column, if the same code has a Large size I need it to return the value from the Large column

The formulas I tried to use:
=INDEX(Table1[[XS]:[XL]],MATCH(1,(Table1[[#All],[Article]]=[@Article])*(Table1[[#Headers],[XS]:[XL]]=[@Size]),0))
=XLOOKUP(1,([@Article]=Table1[Article])*([@Size]=Table1[[#All],[XS]:[XL]]),Table1[[XS]:[XL]])


Any suggestions or help, please?

How about below?

=INDEX(Table1[#All],MATCH([@Article],Table1[[#All],[Article]],0),MATCH([@Size],Table1[#Headers],0))

/Skovgaard

 

[SteynBS]

Have you tried like this?

=INDEX(Table1[#All],MATCH([@Article],Table1[[#All],[Article]],0),MATCH([@Size],Table1[#Headers],0))

Thank you, it works - Thank you for your time and assistance

 

[SteynBS]

How about below?

=INDEX(Table1[#All],MATCH([@Article],Table1[[#All],[Article]],0),MATCH([@Size],Table1[#Headers],0))

/Skovgaard

It works perfect, thank you for your asssitance

 

[Legacy 143009]

Your Welcome

 

[Peter_SSs]

You could also do it like this

=XLOOKUP([@Article],Table1[Article],INDIRECT("Table1["&[@Size]&"]"))