Excel formula to return a cell column and row heading

Hi everyone,

I'm stumped, I've created a table according to shelving in my store room for spares. What I want to do now is create a formula whereby if I enter the part number in a cell on sheet 1 for eg: 10ABC123, I want it in the next cell to search the table on sheet 2 for the part number and tell me the column name and row name i.e. Stand 2; Box 3; Shelf 4 - so by putting that part number in I will know exactly where to go in the storeroom to find that spare. Can anyone help with that formula? I have attached the spreadsheet to make more sense of what I'm trying to do.

Raj Bin.xlsx

=INDEX(TABLES!A1:A26,MIN(IF(TABLES!A1:I26=B4,ROW(TABLES!A1:I26)))-MOD(MIN(IF(TABLES!A1:I26=B4,ROW(TABLES!A1:I26))),3)) & " " & INDEX(TABLES!A2:I2,MIN(IF(TABLES!A1:I26=B4,COLUMN(TABLES!A1:I26))))

It's an array formula so confirm with ctrl+shift+enter.

Based on DaddyLongLegs formula here;
http://www.excelforum.com/excel-form...n-a-table.html

Thank you SO much! Perfect! Please add in the extra formula for the stand name?

=index(tables!1:1,min(if(tables!a1:i26=b4,column(tables!a1:i26)))-mod(min(if(tables!a1:i26=b4,column(tables!a1:i26)))+6,8))&" "&index(tables!a2:i2,min(if(tables!a1:i26=b4,column(tables!a1:i26))))&" "&index(tables!a1:a26,min(if(tables!a1:i26=b4,row(tables!a1:i26)))-mod(min(if(tables!a1:i26=b4,row(tables!a1:i26))),3))

Thanks, it gives me a #VALUE though?

Did you use ctrl+shift+enter?

Oh yes I forgot about that! Thanks SO much for your help! You're a superstar!!!!!

I should have done this earlier, if I added another 4 tables below that one (for 5 stands) how would the formula change? I've attached the new spreadsheet:

Raj Bin.xlsx

Sorry, I was assuming the different stands would be horizontal. To find the stand as you have it would be:
INDIRECT("Tables!B" & MIN(IF(TABLES!A1:I130=B4,ROW(TABLES!A1:I130)))+1-MOD( 26+MIN(IF(TABLES!A1:I130=B4,ROW(TABLES!A1:I130))),26))

It doesn't work for me is there supposed to be an = before INDIRECT? Even if i try it that way it doesn't want to work for me. I don't mind either way, if I put the tables side by side horizontally that would also be fine, how would I alter the formula then?

=INDIRECT("Tables!B" & MIN(IF(TABLES!A1:I130=B4,ROW(TABLES!A1:I130)))+1-MOD( 26+MIN(IF(TABLES!A1:I130=B4,ROW(TABLES!A1:I130))),26)) &" "&INDEX(TABLES!A2:I2,MIN(IF(TABLES!A1:I130=B4,COLUMN(TABLES!A1:I130))))&" "&INDEX(TABLES!A1:A130,MIN(IF(TABLES!A1:I130=B4,ROW(TABLES!A1:I130)))-MOD(MIN(IF(TABLES!A1:I130=B4,ROW(TABLES!A1:I130)))+ROUNDDOWN(MIN(IF(TABLES!A1:I130=B4,ROW(TABLES!A1:I130)))/27,0),3))

Try that one, there were some problems in the shelf formula, I didn't notice you had groups of 2 and 3 cells in the row headers

YES!!!!!! Thank you so much! You're a DARLING!!!!

Not a problem, that one was interesting and I learnt a lot - I'm sure some of the more formula-orientated posters here could come up with something a bit shorter though!

Haha, doesn't phase me really how long the formula is, as long as it works thanks again! You've made my day! Nothing like that feeling when a formula works :D

Hi there,

Sorry to worry you again, but I've opened up my spreadsheet and it doesn't seem to be working again for some strange reason? Its only showing the stand and box number, not the shelf number? I have added in another shelf as well for the last stand. I'd really appreciate your help again

Raj Bin.xlsx

Sorry forgot to attached the spreadsheet. Also can you make it so it shows the stand #, the shelf # and then the box #?