HLOOKUP seeking True/False Row Then Vlookup (or Index) of True Value against array data set

Vana_Whyte

New Member
Joined
Oct 9, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
I am asking for help solving a formula solution which searches an active user input cell across a row (no specific column). Which then, depending on it's True/False outcome in the above row formula, then searches the cell value in a Vlookup or index down through an array of known data. ( Most identifiably, I am trying to circumvent the need to have a Data validation drop down variable, which we be used as part of cumbersome "IF" statement to identify the specific customer column # the input value would search against in the Vlookup) Ideally making the tool user friendly, minimizing the amount of user affectable variables)
Snapshot included, notes as follows:
(=If isblank & =iferror are both present)
Row 1: Unique Customer columns
Row 2: Hlookup determining if Row 3 Cell for each column exists in its below column array
Row 3: User Input "Empty" cells
ColA Row 4 : Col M bottom of array row = known data or Empty cells
Cell M4 = where I would like this formula solution to reflect the vlookup/index result of Column M row equivalent
A3:L3 = Would be single cell - simultaneous user input, multiple value results which equate to incorrect data, the issue would be resolved with up-front communication with the user and not need formula circumvention.

If the simple answer is VBA then I guess Data Validation is my band-aid.

Thank you for any help,
 

Attachments

  • Hlookup Vlookup Index.jpg
    Hlookup Vlookup Index.jpg
    171.7 KB · Views: 19

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,593
Messages
5,838,257
Members
430,536
Latest member
Manoj Gaidhankar

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