0

I am trying to find a way to pull the contents of entire row using vlookup. I can get the formula to work when the value to search for is in column 1, but I need the formula to return the row irrespective of in which column the value been searched is located.

=VLOOKUP($A$8,MainSheet!$A$1:$P$172,COLUMN(MainSheet!A1),FALSE)

In this example, cell A8's value to be referenced is "invalid'.

An example table of what I am trying to do is below:

enter image description here

If "invalid" is in the Item1 column the row returns fine, but I get N/A's if the "invalid" is in columns (Item2, Item3 or Item4). Basically the referenced value "invalid" is not fixed to one column so at the next spreadsheet refresh, it might be in Item2 column instead of Item1.

I also wont be able to use macro's as the workbook will be running from 365's Excel online.

David
  • 127
  • 3
  • 10

1 Answers1

3

If one has the dynamic array formula Filter:

=FILTER(A3:D7,MMULT(--(A3:D7="invalid"),TRANSPOSE(COLUMN(A3:D7)^0))>0)

Put that in the first cell and Excel will spill the results.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thank you Scott. I have marked this as the answer as it is doing exactly what I requested but would also like to know if there is a way to use a cell reference instead of having the word "invalid" in the function. The "invalid" is dynamic and changes between several states, e.g "valid", "blank", etc. So if i want to search for those states, I would just enter them into the reference cell A8 instead of having to amend the formula. I tried with A8, $A8 and $A$8 but the result fails with #CALC!. Will still mark this as the answer – David Sep 16 '20 at 17:33
  • My bad. Works perfectly without the double quotes. Thank you again – David Sep 16 '20 at 17:37