Search table with multiple criteria and return row

I have been working on a variation of this for a long time and I cannot get it to work.

It is better to just post the example and go from there.

I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.

If I've understood... I would do the following (based on what you have presently)

I1:J7 enter field names where I is field <> "" and J is field = "", eg:

Please Login or Register  to view this content.

Then ascertain column positions within your table

Please Login or Register  to view this content.

Clear the present formulae in H1:H7, given you only need output for one given selection at any given time there is really no need to conduct 7 SUMPRODUCTs - 1 will suffice and this can be done in A4 directly such that:

Please Login or Register  to view this content.

For returning table row positions for given selection and again (as above) utilising the table you put into place in I1:J7

Please Login or Register  to view this content.

As you alter your selection in the Form Control so the row list should update accordingly.