How to search cell value in a table

The searching object could be in any row and in any column so lookup alone wont do it.

Also I need it to return value of cell on right of it but formula that would return its position would be great

Upvotes: 1

Answers (3)

Reputation: 1

try:

=VLOOKUP(K4; {FLATTEN(B5:G20)\{QUERY(FLATTEN(B5:G20); "offset 1"; 0); ""}}; 2; 0)

Upvotes: 1

Reputation: 36870

You can also try OFFSET() function with SUMPRODUCT().

=OFFSET(A1,SUMPRODUCT(ROW(A1:G20)*(A1:G20=$I$4))-1,SUMPRODUCT(COLUMN(A1:G20)*(A1:G20=$I$4)))

Upvotes: 1

Reputation: 3802

In K5, enter formula :

=INDEX(A1:I22,SUMPRODUCT(ROW(A1:A22)*(A1:I22=K4)),SUMPRODUCT(COLUMN(A1:I1)*(A1:I22=K4))+1)

Upvotes: 2