Formula Help--XLOOKUP?

[FoodYarnNerd]

I am trying to pull in data from one table into another and cannot figure out how to make it work.

I need to look up both the Material and the Size in a different table and then pull in the value into column V (first snip). However, the table that I need to pull from has the size information running across as column headers so a VLOOKUP will not work (second snip). I was hoping XLOOKUP or INDEX-MATCH would give me what I need but I cannot seem to get any formula to work here. Am I missing something obvious?

Thanks so much for any assistance!

 

[FoodYarnNerd]

Is the data on sheet1 in a structured table, or just a normal range?

RIght now they're both in Tables but they don't have to be. HIX Report is Table3 and Sheet1 is Table1.

 

[Fluff]

OK, in that case make sure that all the values in col U are text. To check you can put this in spare column & fill down

=istext(u2)

make sure they all say True

 

[FoodYarnNerd]

OK, in that case make sure that all the values in col U are text. To check you can put this in spare column & fill down

=istext(u2)

make sure they all say True

Thanks.

Now it's just returning all zeroes.

This is the formula I actually have in my sheet:

=INDEX(Table1,MATCH([@Material],Table1[MATERIAL],0),MATCH([@[b2b qty]],Table1[#All],0))

What am I missing? Would it be helpful to see any more snips or to have additional information?

Thank you!

 

[Fluff]

The second match should be Table1[#Headers] rather than #All

 

[FoodYarnNerd]

The second match should be Table1[#Headers] rather than #All

Okay, I've fixed that and made sure everything is Text in column U and also in Table1 but it's still only pulling in zeroes.

 

[Fluff]

If you are getting 0s then it sounds as though it's finding both values. Try using the evaluate formula button on the formula tab, that way you can step through the calculation, which might show where it's going wrong.

 

[FoodYarnNerd]

If you are getting 0s then it sounds as though it's finding both values. Try using the evaluate formula button on the formula tab, that way you can step through the calculation, which might show where it's going wrong.

That helped! I got it! Thank you SO MUCH for your time today.

 

[Fluff]

Glad it's sorted & thanks for the feedback.