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

censo

Board Regular
Joined
Dec 25, 2015
Messages
147
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHIJ
1LOOKUP VALUEACTUAL SUPP COSTACTUAL SUPP SHIPSKUIDENTIFIERVARSUPP URLIDENTIFIER LINKSKUG COSTSKUG SHIP
2C123WHR2MUABC-12345C123WHR2MUBluehttps://www.sample.com.1234511.985
3ABC-12345-002ABC-12345-002C1239HO98XALargehttps://www.sample.com.123456.985
DESIRED OUTCOME


SMPL.xlsx
ABCDEFGHIJKLMNOP
1local_idvendor_urlvendor_variantvendor_stockvendor_pricevendor_shippingreferencecompare_urlcompare_variantcompare_stockcompare_pricecompare_shippingprofit_formulaselling_formulareprice_storereprice_sku
2#https://www.sample.com.12345Blue111.985ABC-12345C123WHR2MU
3#https://www.sample.com.12345Large16.985ABC-12345-002C1239HO98XA
ARRAY
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 25, 2015
Messages
147
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff 1.xlsm
ABCDEFGHIJ
1LOOKUP VALUEACTUAL SUPP COSTACTUAL SUPP SHIPSKUIDENTIFIERVARSUPP URLIDENTIFIER LINKSKUG COSTSKUG SHIP
2C123WHR2MUABC-12345C123WHR2MUBluehttps://www.sample.com.1234511.985
3ABC-12345-002ABC-12345-002C1239HO98XALargehttps://www.sample.com.123456.985
Sheet1
Cell Formulas
RangeFormula
D2:D3D2=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:E3E2=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:F3F2=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
 
Solution

censo

Board Regular
Joined
Dec 25, 2015
Messages
147
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
Yes you can replace them with named ranges, if the col G was called Reference the formula would become
Excel Formula:
=INDEX(Reference,AGGREGATE(15,6,(ROW(Reference)-MIN(ROW(Reference))+1)/((Reference=$A2)+(Sheet2!$P$2:$P$3=$A2)),1))
 

censo

Board Regular
Joined
Dec 25, 2015
Messages
147
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
In sheets you would use the Filter function, something like
Excel Formula:
=FILTER(Sheet2!G2:G3,(Sheet2!G2:G3=A2)+(Sheet2!P2:P3=A2))
 

censo

Board Regular
Joined
Dec 25, 2015
Messages
147
Office Version
  1. 2013
Platform
  1. Windows
That totally worked. Thanks so much Fluff!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,637
Messages
5,719,972
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top