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')
If someone could help me untangle the IF's or suggest a different function, I'd appreciate it.
It'd probably be easier if you broke out your formula into recognizable parts with variables using a Let formula 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"))