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


New Member
Oct 9, 2020
Office Version
  1. 2016
  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,


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

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Latest member