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

 

[awoohaw]

@EddieD , it would be helpful to know which formula you are trying to use. I for one, am unsure if you are asking for a pure lookup or a sum of lookups (which @DanteAmor and @Fluff have provided solutions for, I think).

 

[DanteAmor]

Check the name of the sheet.
Try:

=XLOOKUP(A2&B2,'Tab 2'!A:A&'Tab 2'!B:B,'Tab 2'!C:C)

 

[awoohaw]

Check the name of the sheet.
Try:

=XLOOKUP(A2&B2,'Tab 2'!A:A&'Tab 2'!B:B,'Tab 2'!C:C)

I assumed that tab1 and tab2 were just fake tab names for his example.
@EddieD , but @DanteAmor 's formula does stress using apostrophes when there are blanks in the Tab name. So, please look at that.

 

[EddieD]

Which formulae are you referring to?

Sorry my 'quoting' wasn't working. I'll respond again.

 

[EddieD]

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)

Hi there.
This was the solution that ultimately worked. Thank you very much.

 

[awoohaw]

Hi there.
This was the solution that ultimately worked. Thank you very much.

happy you found a solution. @DanteAmor 's is essentially the same, and will ensure tabs with spaces will be located.

Best wishes.