Returning a value to a cell based off location in a table

[lance.husak]

Hi all,

I need some help with a formula that does the following: (see attached excel file)

I want to search the table in Sheet 1 and place the row header (S1,S2,S3, etc) that matches the respectful number in Column A on sheet StudentRooms in Column E.

Then I want to do the same type of function in Column F but returning the respectful time in sheet1.

I can't figure out how to make this happen.

Any help would be appreciated.

Thanks!

Lance

[Carmandol]

When you say "respectful", do you mean "corresponding"?

[Carmandol]

Why did you put all the headers in the first cell? You should put one header for every column. That would be the topmost cell of each column. That's why it's called "header", it's heading the column.

[lance.husak]

Why did you put all the headers in the first cell? You should put one header for every column. That would be the topmost cell of each column. That's why it's called "header", it's heading the column.

For the program i extract this into...i cant have headers. Also, respectful is corresponding...sorry about that lol

[FDibbins]

I am very confused as to what you want here Can you give some sample answers and explain how you got them?

[Beamernsw]

Hi Lance, try these:-

Cell E3: =INDEX(Sheet1!$B$1:$J$1,SMALL(IF(COUNTIF(A3,Sheet1!$B$2:$J$21)>0,COLUMN(Sheet1!$B$2:$J$21)-1),1))
Cell F3: =INDEX(Sheet1!$A$2:$A$21,SMALL(IF(COUNTIF(A3,Sheet1!$B$2:$J$21)>0,ROW(Sheet1!$B$2:$J$21)-1),1))

These are array formulas, therefore after you paste these in the cell you need to press F2 and then hold down Ctrl+Shift while pressing enter.