Dynamic Function Where One of Two Possible Values May be Used as a Lookup

[censo]

SITUATION
I currently have a sheet (WS1) where a user can enter a value in Column A and Columns D:J return specific values from an array located on WS2 (the order in which the columns are arranged in Columns D:J are specific and not in the order in which they appear on WS2, so VLOOKUP was not an option).


PROBLEM
The solution works great if only the one type of value is used to return the other missing values. I want to introduce the ability to use one value (the current solution) -or- a second value to use as a lookup, but I am limited in knowledge on how to integrate this into what's currently in place.


GREATER DETAIL
The pairings between the two worksheets are as follows:

WS1 D2:D to WS2 G2:G
WS1 E2:E to WS2 P2:P
WS1 F2:F to WS2 C2:C
WS1 G2:G to WS2 B2:B
Ignore Column H
WS1 I2:I to WS2 E2:E
WS1 J2:J to WS2 F2:F

1. Column H in WS1 does not return anything from WS2 because I use a HYPERLINK function to create what I need there.
2. If SKU is used as a lookup value, it resides on WS2 G2:G
3. If Identifier is used as a lookup value, it resides on WS2 P2:P
4. The rows in WS2 increase or decrease regularly so I would prefer to use a Dynamic Named Range vs. absolute cell referencing.

OBJECTIVE
No macro required, strictly function based.

The only two values that could be used as lookups reside in WS2 Column G or WS2 Column P

Either value can be used as a lookup to return the remaining needed values in WS1 Columns D:J. I would be open to creating a separate Column B if it makes it easier to use for the second value as a lookup for proper functionality purposes. (i.e., if Value 1 is used, enter in Column A, if Value 2 is used, enter in Column B).

Diagrammatically, observe below where two different values are entered and all the remaining values are returned (this would be the desired outcome).

SMPL.xlsx
	A	B	C	D	E	F	G	H	I	J
1	LOOKUP VALUE	ACTUAL SUPP COST	ACTUAL SUPP SHIP	SKU	IDENTIFIER	VAR	SUPP URL	IDENTIFIER LINK	SKUG COST	SKUG SHIP
2	C123WHR2MU			ABC-12345	C123WHR2MU	Blue	https://www.sample.com.12345		11.98	5
3	ABC-12345-002			ABC-12345-002	C1239HO98XA	Large	https://www.sample.com.12345		6.98	5
DESIRED OUTCOME

SMPL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	local_id	vendor_url	vendor_variant	vendor_stock	vendor_price	vendor_shipping	reference	compare_url	compare_variant	compare_stock	compare_price	compare_shipping	profit_formula	selling_formula	reprice_store	reprice_sku
2	#	https://www.sample.com.12345	Blue	1	11.98	5	ABC-12345									C123WHR2MU
3	#	https://www.sample.com.12345	Large	1	6.98	5	ABC-12345-002									C1239HO98XA
ARRAY

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[censo]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for the tip! I'll update that info right now.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	LOOKUP VALUE	ACTUAL SUPP COST	ACTUAL SUPP SHIP	SKU	IDENTIFIER	VAR	SUPP URL	IDENTIFIER LINK	SKUG COST	SKUG SHIP
2	C123WHR2MU			ABC-12345	C123WHR2MU	Blue	https://www.sample.com.12345		11.98	5
3	ABC-12345-002			ABC-12345-002	C1239HO98XA	Large	https://www.sample.com.12345		6.98	5
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=INDEX(Sheet2!$G$2:$G$100,AGGREGATE(15,6,(ROW(Sheet2!$G$2:$G$100)-ROW(Sheet2!$G$2)+1)/((Sheet2!$G$2:$G$100=$A2)+(Sheet2!$P$2:$P$3=$A2)),1))
E2:E3	E2	=INDEX(Sheet2!$P$2:$P$100,AGGREGATE(15,6,(ROW(Sheet2!$G$2:$G$100)-ROW(Sheet2!$G$2)+1)/((Sheet2!$G$2:$G$100=$A2)+(Sheet2!$P$2:$P$3=$A2)),1))
F2:F3	F2	=INDEX(Sheet2!$C$2:$C$100,AGGREGATE(15,6,(ROW(Sheet2!$G$2:$G$100)-ROW(Sheet2!$G$2)+1)/((Sheet2!$G$2:$G$100=$A2)+(Sheet2!$P$2:$P$3=$A2)),1))

I have done cols B:F

 

[censo]

Ok, how about ...

Whoa - very elegant solution and worked right out of the gate for me in my sample workbook. I've not seen something like this before, thank you so very much!

Safe to assume that where instances of "Sheet2!$G2:$G$100" (et. al) are found that I can replace with a Dynamic Named Range so that I don't run out of rows?

 

[Fluff]

Yes you can replace them with named ranges, if the col G was called Reference the formula would become

=INDEX(Reference,AGGREGATE(15,6,(ROW(Reference)-MIN(ROW(Reference))+1)/((Reference=$A2)+(Sheet2!$P$2:$P$3=$A2)),1))

 

[censo]

Excellent, thank you.

One final question on this topic: I learned that the solution you identified which works beautifully in Excel, sadly does not work in Google sheets because it does not recognize the AGGREGATE command (we may be migrating to that environment so I did it as a preliminary test).

Would you by any chance happen to know an alternate approach to that, or do you recommend I create an entirely new thread for that question?

 

[Fluff]

In sheets you would use the Filter function, something like

=FILTER(Sheet2!G2:G3,(Sheet2!G2:G3=A2)+(Sheet2!P2:P3=A2))

 

[censo]

That totally worked. Thanks so much Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.