• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking up a value in an array

N

negot8or

New Member
I'm building a tile wall where each tile is associated with an artist and the tiles have all been numbered. The tiles will not be in numeric order and I've created a sheet where all of the tile "slots" will eventually be filled in with the numbers associated with each tile. For example:

Code: 
    A    B    C    D
1  465  726  896  473
2  987  234  123  908
3  836  293  358  192

All of the values in this array will be unique.

On another sheet, I have a list of all of the artists and their tile numbers. I would like to populate a column that contains the cell reference from the tile slot sheet:

Code: 
Tile#  Name  SlotRef
293    Me       <formula to pull back "B3">

I've tried this with everything I can think of but get nowhere. I have actually added a row and column at the top and left of my Tile Slot sheet:

Code: 
    A     B    C    D    E
1          1    2    3    4
2   A     465  726  896  473
3   B     987  234  123  908
4   C     836  293  358  192

My hope in adding these extra labels would be that I could do two reverse lookups - find 293, return the value from Column A and the value from Row 1. Then I could combine them into a full SlotRef number.
My ultimate goal is to be able to produce a key so that visitors and artists seeing the wall will be able to find an artist's name, see the SlotRef number and use it to locate the actual tile.
Any suggestions? Thanks!
 
Using the first table:

=ADDRESS(INDEX(ROW(Sheet1!A1:A4),SUMPRODUCT((Sheet1!A1:D4=A10)*(Sheet1!ROW(A1:D4)))), INDEX(COLUMN(Sheet1!A1:D1),SUMPRODUCT((Sheet1!A1:D4=A10)*(COLUMN(Sheet1!A1:D4)))),1,1) Ctrl+Shift+Enter

Where the Lookup Value is in A10 on Sheet2

Read how this works here:
http://chandoo.org/wp/2015/10/15/fo...ell-address-for-a-value-2d-3d-reverse-lookup/

Providing a sample file is by far the easiest way to get a targeted result.
 
Last edited:
Thanks for the assistance so far. Attached is the sheet. See tab TileList and tab GridTileNu.
 

Attachments

  • TileWall_Chandoo.xlsx
    53 KB · Views: 3
So... I made it work given your response, Hui. That was awesome. Thanks!Now I need to lookup the Grid coordinates from the GridCellNu table (or the values from Column A and Row 1). Sorry I've apparently lost my mind.
 
My formula above is incorrect
it shoukld be:
=ADDRESS(INDEX(ROW(Sheet1!A1:A4),SUMPRODUCT((Sheet1!A1:D4=A10)*(ROW(Sheet1!A1:D4)))), INDEX(COLUMN(Sheet1!A1:D1),SUMPRODUCT((Sheet1!A1:D4=A10)*(COLUMN(Sheet1!A1:D4)))),1,1) Ctrl+Shift+Enter
 
Hui said:
My formula above is incorrect
it shoukld be:
=ADDRESS(INDEX(ROW(Sheet1!A1:A4),SUMPRODUCT((Sheet1!A1:D4=A10)*(ROW(Sheet1!A1:D4)))), INDEX(COLUMN(Sheet1!A1:D1),SUMPRODUCT((Sheet1!A1:D4=A10)*(COLUMN(Sheet1!A1:D4)))),1,1) Ctrl+Shift+Enter
Click to expand...

Thank you!
 
First Delete the Top Row and First Column in GridTileNu

Then in TileList F2: put:
=ADDRESS(INDEX(ROW(GridTileNu!$A$1:$AR$16),SUMPRODUCT((GridTileNu!$A$1:$AS$16=A2)*(ROW(GridTileNu!$A$1:$A$16)))), INDEX(COLUMN(GridTileNu!$A$1:$AR$1),SUMPRODUCT((GridTileNu!$A$1:$AR$16=A2)*(COLUMN(GridTileNu!$A$1:$AR$16)))),1,1) Ctrl+Shift+Enter
Copy down
Now populate the GridTileNu worksheet

I don't know why you need the GridCellNu worksheet?

enjoy

see attached file
 

Attachments

  • TileWall_Chandoo.xlsx
    41.2 KB · Views: 0
Hui said:
First Delete the Top Row and First Column in GridTileNu

Then in TileList F2: put:
=ADDRESS(INDEX(ROW(GridTileNu!$A$1:$AR$16),SUMPRODUCT((GridTileNu!$A$1:$AS$16=A2)*(ROW(GridTileNu!$A$1:$A$16)))), INDEX(COLUMN(GridTileNu!$A$1:$AR$1),SUMPRODUCT((GridTileNu!$A$1:$AR$16=A2)*(COLUMN(GridTileNu!$A$1:$AR$16)))),1,1) Ctrl+Shift+Enter
Copy down
Now populate the GridTileNu worksheet

I don't know why you need the GridCellNu worksheet?

enjoy

see attached file
Click to expand...

You're right... I wouldn't. Thank you again very much!
 
Hui said:
First Delete the Top Row and First Column in GridTileNu

Then in TileList F2: put:
=ADDRESS(INDEX(ROW(GridTileNu!$A$1:$AR$16),SUMPRODUCT((GridTileNu!$A$1:$AS$16=A2)*(ROW(GridTileNu!$A$1:$A$16)))), INDEX(COLUMN(GridTileNu!$A$1:$AR$1),SUMPRODUCT((GridTileNu!$A$1:$AR$16=A2)*(COLUMN(GridTileNu!$A$1:$AR$16)))),1,1) Ctrl+Shift+Enter
Copy down
Now populate the GridTileNu worksheet

I don't know why you need the GridCellNu worksheet?

enjoy

see attached file
Click to expand...

Well... I forgot something. The artists are kids. It's easier to do A-P on the row labels and 1-44 on the column labels, rather than 1-16 and A-AR, which is what the GridCellNU worksheet would resolve. Is there a simple way to take the cell reference your formula creates and simply look up its match in the GridCellNu sheet to pull the value in GridCellNu?
 
How about
upload_2016-5-19_23-35-21.png

Change TileList F2: to:
="R"&INDEX(ROW(GridTileNu!$A$1:$AR$16),SUMPRODUCT((GridTileNu!$A$1:$AS$16=A2)*(ROW(GridTileNu!$A$1:$A$16))))&" C"&INDEX(COLUMN(GridTileNu!$A$1:$AR$1),SUMPRODUCT((GridTileNu!$A$1:$AR$16=A2)*(COLUMN(GridTileNu!$A$1:$AR$16)))) Ctrl+Shift+Enter

Copy down
 
You must log in or register to reply here.
Back
Top