CODEWITHSUNDEEP
excelif-statementnestedxlookup
Scott Saxton
Scott Saxton

Reputation: 33

Complicated xlookup across two sheets with multiple criteria

I have a worksheet that I want to pull data from one sheet to another but there is some logic that makes it complicated.

Master sheet with the raw data:

COL_H COL_I COL_J COL_K COL_L
SKU_R SKU_L OnHandP OnHandL OnHandR
------- --------- --------- --------- ---------
334 222 25 19 422
------- --------- --------- --------- ---------
456 456 0 219 22
------- --------- --------- --------- ---------
889 334 0 99 33
------- --------- --------- --------- ---------

Locations sheet:

COL_B COL_F COL_H
SKU R,L,P Actual
--------- --------- ---------
456 L 219
--------- --------- ---------
334 R 422
--------- --------- ---------

psudo-logic: First, all the R's, L's, and P's mean Right, left or Pair. The Locations sheet will tell if the SKU is a left, right, or Pair (COL_F). I need to take that information and find the SKU in the Mastersheet and depending on it's R,L,P value, I need to look in either the SKU_R or SKU_L column to find it and then grab the value from the OnHandL or OnHandR column then show that amount in the COL_H Actual cell.

I've tried some stuff like this and it did not work out at all.

=XLOOKUP(B2,
if(F2='R',Master!H:H,
if(F2='L',Master!I:I,
if(F2='P',Master!H:H))),'N/A',
    if(F2='R',Master!K:K,
    if(F2='L',Master!L:L,
    if(F2='P',Master!J:J))),'N/A')

Could you help me untangle the IF's or suggest a different function?

Upvotes: 0

Views: 105

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9932

It'd probably be easier if you broke out your formula into recognizable parts with variables using a Let formula (I wrote this article) so you can see what you're doing easier. Using the below formula, you can more clearly see what your variables are, then just embed an if statement in the xlookups lookup column and return column parameters.

Also note that you have pairLookup going to column H (same as right?).

While long, this is a pretty simple lookup, so just tinker with it testing each parameter at a time and it should work. There's also much more efficient ways to right this such as creating a lookup array of {R,L,P} and then using a choose statement.

=LET(rightLookupCol,Master!H:H,
   leftLookpCol,Master!G:G,
   pairLookup,Master!H:H,
  rightReturnCol,Master!K:K,
  leftReturnCol,Master!K:K,
  lookupValue,F2,
pairReturn,Master!J:J,XLOOKUP(B2,IF(lookupValue="R",rightLookupCol,IF(lookupValue="L",leftLookpCol,pairLookup)),IF(lookupValue="R",rightReturnCol,IF(lookupValue="L",leftReturnCol,pairReturn)),"no value found"))

Upvotes: 1

Black cat
Black cat

Reputation: 6271

Some notes to your formula:

  • Use SEARCH or FIND to look for substring match in strings
  • The parameter sequence is not corresponding to XLOOKUP spec.

According to your comment this formula does the following

Side Match Column Return Column
L I:I K:K
R H:H L:L 
=XLOOKUP(B2,IF(IFERROR(SEARCH("R",F2),0)>0,Master!H:H,
IF(IFERROR(SEARCH("L",F2),0)>0,Master!I:I,"N/A")),
IF(IFERROR(SEARCH("R",F2),0)>0,Master!L:L,
IF(IFERROR(SEARCH("L",F2),0)>0,Master!K:K,"N/A")))

The formula applies a priority order of R then L if Side contains both

Formula result

SKU Side Return Value
334 L 99
334 R 422
456 L 219
456 R 22
456 LR 22
334 LR 422
334 RL 422

Upvotes: 0

Related Questions