XLOOKUP - if 2 cells = certain criteria, then return 3rd value.

[EddieD]

Hi all,
im struggling with this one a bit.

I have 2 tabs with almost the same data, except my Tab2 contains more complex calculations to get a result. That result (in column C) i want to place on TAB 1 (same column).

I would like to perform an X Lookup, that says - Lookup the SKU in Tab1, A1, and make sure that it matches B2 on the same row, then produce the result from column C.



TAB 1

SKU	Location	January Sales QTY
Car-123	USA	Return figure from Tab 2
Car-123	Canada	Return figure from Tab 2
Car-456	USA	Return figure from Tab 2
Car-456	Canada	Return figure from Tab 2

TAB 2

SKU	Location	Jan Sales QTY
Car-123	USA	125
Car-123	Canada	667
Car-456	USA	441
Car-456	Canada	335

 

[DanteAmor]

Hi @EddieD . Thanks for posting on the forum.

Try this:

Dante Amor
	A	B	C
1	SKU	Location	January Sales QTY
2	Car-123	USA	125
3	Car-123	Canada	667
4	Car-456	USA	441
5	Car-456	Canada	335
TAB 1

Cell Formulas
Range	Formula
C2:C5	C2	=SUMPRODUCT(('TAB 2'!$A$2:$A$5=A2)*('TAB 2'!$B$2:$B$5=B2)*('TAB 2'!$C$2:$C$5))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[awoohaw]

in Cell C2 of Tab1 try:

= XLOOKUP(A2&B2,Tab2!A2:A50&Tab2!B2:B50,Tab2!C2:C50)

proof of concept:

Book1
	A	B	C	D	E	F	G	H
1	SKU	Location	Jan Sales QTY
2	Car-123	USA	125			Car-123	Canada	667
3	Car-123	Canada	667
4	Car-456	USA	441
5	Car-456	Canada	335
Sheet1

Cell Formulas
Range	Formula
H2	H2	=XLOOKUP(F2&G2,A2:A5&B2:B5,C2:C5)

 

[Fluff]

Another option

=SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,B2)

 

[EddieD]

Thank you.
This was similar to my first try - but i keep showing a #VALUE! error. Even though there is data there.

 

[Fluff]

This was similar to my first try

Which one, there are 3 different formulae.

 

[awoohaw]

Thank you.
This was similar to my first try - but i keep showing a #VALUE! error. Even though there is data there.

Are you trying to get a sum of matches? I did not read your question that way. But, if so... you probably have some values that excel thinks are text. Format column C as general or number, and then do a text to column on column C to be sure you convert everything the numeric values.

 

[EddieD]

This one I get a #SPILL! error.

 

[EddieD]

Thank you again - let me continue to work on it.

 

[Fluff]

Which formulae are you referring to?