excel - Complicated xlookup across two sheets with multiple criteria - Stack Overflow

admin管理员组

文章数量:1297034

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
-------	---------	---------	---------	---------

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?

• excel
• if-statement
• nested
• xlookup

Share Improve this question Follow edited Feb 14 at 13:26 desertnaut 60.4k3232 gold badges152152 silver badges180180 bronze badges asked Feb 11 at 16:25 Scott SaxtonScott Saxton 3344 bronze badges 5

• I fot a column in the master sheet. It is Master!J:J which is OnHandP. – Scott Saxton Commented Feb 11 at 16:27
• 2 Just edit the question – rotabor Commented Feb 11 at 16:30
• Add more lines of sample data. Maybe six lines of lookups and how they should be successfully retrieved. – pgSystemTester Commented Feb 11 at 17:32
• I added some sample data. Honestly, I'd ignore the P (pairs) indicator. I don't l know what they want me to do with it. Once I get this figured out with the R and L indicators, I can expand for the P's later. – Scott Saxton Commented Feb 11 at 18:31
• Please modify your question according to your comment if you have additional or changed info on the issue to give a more exact answer for it. – Black cat Commented Feb 12 at 5:46

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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"))

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

本文标签： excelComplicated xlookup across two sheets with multiple criteriaStack Overflow