LOOKUP (+VLOOKUP & +HLOOKUP) not able to search/return value from entire table?

Hi Wesley,

I have a coulple of questions.

What are you trying to accomplish? What are you going to use the information for? This is the big one. There may be an approach that sidesteps your difficulties.

There may be a way to get LOOKUP() to work but it will settle for a close match if it can't find your value. So the question is this, is every value that you are looking for represented in the table? In your example, LOOKUP() would return a value if you looked for 26 or if you were missing a number in a sequence it would return a close match. This may not be good.

Is your data table likely to grow? Or is 5x5 it?

You want the Row and Column names in separate columns. Why?

quinn

Hi Wesley,

Here is a possible solution.

The formula in column return and row return simply copies the header. i.e. G2 =A2.

Here is an overview. We will search the columns to discover what row and search rows to discover what column.

Search columns.

B2 =IFERROR(VLOOKUP($A2,Data Table::B$2:$G$7,8−COLUMN(),FALSE),"")

This is filled across to F2 and then filled down.

You can hide columns B-F.

G2 =CONCATENATE(B2,C2,D2,E2,F2)

This is filled down.

So that was easy.

Searching the rows.

H2 =IFERROR(HLOOKUP($A2,Data Table::B$2:$F$7,6,FALSE),"")

We need to tweak this formula for each column.

I2 =IFERROR(HLOOKUP($A2,Data Table::$A$3:$F$7,5,FALSE),"")

There are 2 places the formula changes. The rows-range and the return-row. As the range shrinks, the return row does too.

Once you have the formula adjusted for all of row 2 you can fill down.

M2 can be copied and pasted from G2 and filled down.

M2 =CONCATENATE(H2,I2,J2,K2,L2)

You can hide rows H-L.

You are done.

Sure hope the screenshots show.😁

let me know if you have any questions.

quinn