Look Up returning multiple columns and rows

[twnovak]

I'm working on a database right now for our school, similar to a directory. What I want to do is be able to look up what items a staff member has been issued and when/if they were returned. What I have right now is one sheet with all the information about the staff that looks like the one below. I want to create a separate sheet where you could look up a staff member by last name and can find all the information in the array relative the the specific cell looked up.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Position[/TD]
[TD]Building[/TD]
[TD]Items[/TD]
[/TR]
[TR]
[TD]Doe[/TD]
[TD]Jane[/TD]
[TD]Principal[/TD]
[TD]High School[/TD]
[TD]Cell Phone[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ID Badge[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Keys[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Radio[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]HR Director[/TD]
[TD]Admin Ctr.[/TD]
[TD]Cell Phone[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ID Badge[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]iPad[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Keys[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Projector[/TD]
[/TR]
</tbody>[/TABLE]



So, on the "lookup" sheet, I want to be able to input either Doe and have the look up return cells B2:E6, or if I input Smith it should return cells B7:D11.

I've been experimenting with Vlookup, hlookup, index, and match but haven't been able to get exactly what I want. Any help would be much appreciated.

 

[Steveie]

As a work around could you have the items going across columns rather than down rows.

The for example D1 = Cell Phone to D4 = Radio at the end you could put in cell D5:

=D1&","&D2&","&D3&","&D4

Then lookup to this column D5 ?

 

[twnovak]

As a work around could you have the items going across columns rather than down rows.
The for example D1 = Cell Phone to D4 = Radio at the end you could put in cell D5:
Then lookup to this column D5 ?

Thanks for the suggestion. I originally had a column for Item 1 - Item 5, but my supervisor wants to also keep track of when the items were checked out, when they were returned, and who they were returned to. That would mean I'd need 20 columns, which doesn't look good, so he wants it in the format above.

Do you know how to have an equation that would return the results 6 cells right and 2 cells down from the cell you looked up, or return a group of cells like B2:E5?

 

[bschwartz]

I've come up with a kind of solution. It's a little odd, but might do the trick. Let me know if this works for you.

I have the inputs here:

Sheet15

	A	B	C	D	E
Last Name	First Name	Position	Building	Items
Doe	Jane	Principal	High School	Cell Phone
ID Badge
Keys
Radio
Smith	John	HR Director	Admin Ctr.	Cell Phone
ID Badge
iPad
Keys
Projector
Cooper	Ryan	Teacher	Elementary	Cell Phone
ID Badge
Laptop

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Entering last name in A1 here, and getting the results starting in A3. I'll show all three results:

	A	B	C	D	E
Doe	Jane	Principal	High School	Cell Phone
				ID Badge
				Keys
				Radio

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Doe[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet16​

	A	B	C	D	E
Smith	John	HR Director	Admin Ctr.	Cell Phone
				ID Badge
				iPad
				Keys
				Projector

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Smith[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>

Sheet16​

	A	B	C	D	E
Cooper	Ryan	Teacher	Elementary	Cell Phone
				ID Badge
				Laptop

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Cooper[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

</tbody>

Sheet16​

B2 -->

=IF(ISNA(MATCH(FALSE,NOT(ISTEXT(INDEX(Sheet15!$A$2:$A$30,MATCH($A$1,Sheet15!$A$2:$A$30,0)+1):Sheet15!$A$30)),0)),999,MATCH(FALSE,NOT(ISTEXT(INDEX(Sheet15!$A$2:$A$30,MATCH($A$1,Sheet15!$A$2:$A$30,0)+1):Sheet15!$A$30)),0))

Confirmed with Ctrl+Shift+Enter ^^^.

A3 (and filled down and across) -->

=IF(ROWS(A$3:A3)<=$B$1,SUBSTITUTE(OFFSET(Sheet15!$A$1,MATCH($A$1,Sheet15!$A$2:$A$30,0)+ROW(Sheet15!$A1)-1,COLUMN(Sheet15!A$1)-1),0,"",1),"")

 

[twnovak]

BSchwartz, this seems to be working with a few glitches. Some of the dates are returning without zeros and a few of the cells are returning blank. Could I PM you with and we could exchange sheets?

 

[twnovak]

I figured it out the glitch with the cells returning blank. Haven't been able to figure out why they are returning some without the zeros in the date (i.e. 2012 returns 212)

 

[BrightAndAlert]

How about creating a pivot table?

In order to make it work you would need to fill out last name, first name etc in all the lines. But it can easily be fixed by referencing to the cell above. I.e. filter on the blank cells, if forinstance a3 is blank =a2. Then copy down to the other blank cells.
Then create the pivot table...

 

[bschwartz]

I know why that's happening. I need to modify the formula.

Be back soon...

 

[bschwartz]

I wasn't aware that you had dates in the dataset. Try this instead:

=IF(AND(ROWS(A$3:A3)<=$B$1,OFFSET(Sheet15!$A$1,MATCH($A$1,Sheet15!$A$2:$A$30,0)+ROW(Sheet15!$A1)-1,COLUMN(Sheet15!A$1)-1)<>0),OFFSET(Sheet15!$A$1,MATCH($A$1,Sheet15!$A$2:$A$30,0)+ROW(Sheet15!$A1)-1,COLUMN(Sheet15!A$1)-1),"")