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
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).
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
- Column H in WS1 does not return anything from WS2 because I use a HYPERLINK function to create what I need there.
- If SKU is used as a lookup value, it resides on WS2 G2:G
- If Identifier is used as a lookup value, it resides on WS2 P2:P
- 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 |