Reverse Index/Match?

[mhenk]

Hey all:

I've got a chess board in B2:I9, and would like a function that returns the location of a particular piece.

Say I have Q1 in cell B9, I'd like to return A-1

B1:I1 = A ---> H
A2:A9 = 8 ---> 1

Any help?

 

[pgc01]

Hi Jobowo

Thanks for testing my formula. Any error you find will be an opportunity to improve it.

I could not, however, reproduce the error. This is what I've done:

I started with my solution in Post#3. The data table is in A1:I9 and the results table in K2:L6.

I then followed the steps you mentioned in your post, first:

... moving the array down and right using insert rows/columns ...

I inserted 3 rows before row 1.

The data table is now in A4:I12 and the results table in K5:L9.

I inserted 2 columns before column A.

The data table is now in C4:K12 and the results table in M5:N9.

... and then moving it back using range selection and drag-and-drop

I selected the source data table in C4:K12 and moved it back to A1:I9

I selected the results data table in M5:N9 and moved it back to K2:L6

After each step all the results held.

I could not make the formulas fail as a result of a change in location of the data. I tried several Insert/Delete Rows/Columns and also selecting the tables and moving them around and could not find any problem with the formulas.

I'd be thankful if you'd post the exact steps of your test, with the errors you get, so that I can study this issue.

 

[Jobowo]

I have discredited you unfairly pgc01. I was testing this morning and seeing all manner of strange things. It was just as I was folding up that I noticed your own formula had crashed and assumed the worst. But when I just re-opened the workbook it turned out that it had crashed only because in my last test, I had mistakenly deleted the K3; the resulting #REF caused the crash, not a fault in the formula. Apologies for sending you on a wild goose chase.

Furthermore your formula does remain robust, even though the drag-copy and insert/delete do result in different adjustments to (and subsequent errors in) array constructions by Excel that I have adopted from examples provided by other learned folk on this board.

However, this time I want to be more sure of my findings before I post anything further on this!

 

[pgc01]

I'm glad the formula works fine. If, however, in your future tests you find an error, please post it.

Cheers.

 

[thepaulcook]

Hi this formula works but why is MIN used?

 

[thepaulcook]

sorry I was referring to this formula =INDEX($A$1:$I$1,MIN(IF($B$2:$I$9=$B$12,COLUMN($B$2:$I$9))))&"-"&INDEX($A$1:$A$9,MIN(IF($B$2:$I$9=$B$12,ROW($B$2:$I$9))))