Complex "Lookup" question 2

[Scott24x7]

Hi,
I have a spreadsheet that is a "floor plan", which uses 8 cells (in the same column) to store inforation about the position, such as Name, Extension #, # of LAN ports. I need to make a "List" in another sheet, which has the existing position number, but I need to populate the columns with things like Name, Extension, etc.
I use VLOOKUP and HLOOKUP all the time, but looking at a whole floor, this is impossible, because desks are all in one straight row or column only (and I have 4 floors, with different layouts).
Is there some way that I can have it look at a range of cells, say A1:Z200, and finds a value (that is provided from my Sheet1 (looking in Floor2 for example) that when I find the position number in the full "grid" that I can pluck out data from the row 6 below it? (I can then figure out the rest).
I've looked at INDEX, but it still seems to want everything ordered in defined rows and columns with the same data.
In a worst case scenario, a Macro will work, but I'd rather do something in-sheet at the cell level.
Many thanks!

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Hi,

Please post an example of the data you have to lookup, along with the givens and expected results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Hi Skip,
Hard for me to post... a bit big, but let me see if I can explain a quick scenario:

A12345           A12347        
Bob              Jim           
x2334            x1234         B12222
                               Jack
                               X5727       A15592
                                           Jill
                               B13333      x2121
A23234                         Jenny
Skip                           x1122
x1375

So, each group of 3 above represents a "Desk" position. The data is always in equal rows under the value. What I need to do is create in a separate sheet2 I have Position number populated. I need to search "Floor1" for occurrence of the value in columnA (Position) in Floor1, and return Name and Extension to Sheet2:

Position Name Extension
A12345 Bob x2334
A12347 Jim x1234
B12222 Jack x5727
...
A23234 Skip x1375

Thereby creating an organized column/row list of all the data that is scattered (but grouped) in another sheet.

Does that make sense?
Thanks

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Oh, by the way, I'm using Excel 2010, so any solution that works in a prior version should be fine here.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[kjv1611]

Perhaps using the Find Function?

 

[SkipVought]

You never define what "Floor1" is.

Is the Floor1 sheet, the "map," while sheet2 will be a table, that is constructed from the data on the map?

Will there need to be some sort of relative reference for where the position is located on the "map?"



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Floor1 is just a sheet name. It represents positions with 3 cells, in a column.

Sheet1 is where I'm building the "list". The "Position" is populated in the list. I want to "scan" the Floor1 sheet for an instance of the number in the list, and populate the the other columns with the data from the other 2 cells below the position number.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Copy this to a MODULE.

Run like any other spreadsheet function.

Argument 1: reference the Position Code
Argument 2: either reference "Name" or "Extension", 1 or 2

Function MyLookup(POSITION, ITM) As String
    Dim rFound As Range
    Select Case UCase(ITM)
        Case "NAME"
            ITM = 1
        Case "EXTENSION"
            ITM = 2
    End Select
    Select Case ITM
        Case 1, 2
            Set rFound = Sheet3.Cells.Find(POSITION)
            If Not rFound Is Nothing Then
                MyLookup = rFound.Offset(ITM).Value
            Else
                MyLookup = ""
            End If
            Set rFound = Nothing
        Case Else
            MyLookup = ""
    End Select
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

You could use SUMPRODUCT to find the row and column positions, although this would fail if there are multiple occurences of a location in the Floor1 sheet.

This would give the name ( assuming that you have a location in A2 ):

=INDEX(Floor1!$A$1:$H$16,SUMPRODUCT((Floor1!$A$1:$H$16=$A2)*(ROW(Floor1!$A$1:$H$16)))+1,SUMPRODUCT((Floor1!$A$1:$H$16=$A2)*(COLUMN(Floor1!$A$1:$H$16))))

Note that the reference area starts at Floor1!$a$1 ... the row and column calcs rely on that.

Change the +1 to +2 to get extension.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Glenn,

Excellent spreadsheet solution!!! ==> [purple]*[/purple]

I'd make one very minor change, where my FORMULA is in G2...
[tt]
G2: =INDEX(Floor1!$A$1:$H$16,SUMPRODUCT((Floor1!$A$1:$H$16=$A2)*(ROW(Floor1!$A$1:$H$16)))+[red]G$1[/red],SUMPRODUCT((Floor1!$A$1:$H$16=$A2)*(COLUMN(Floor1!$A$1:$H$16))))
[/tt]
and G1 & H1 have 1 & 2 respectively.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Thanks Skip

The minor change is a good idea.



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Scott24x7]

Glenn,
Looks like the kind of elegant solution I'm looking for. I'll give it a go tomorrow, as it's nearly 1:00am in Tokyo.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Glenn,
Wow... that is one of the most ridiculously useful in-cell calculations I've ever seen. This certainly gets a star, and then some. This will solve a heap of problems for me, and make it a lot easier to validate list data, not just this time, but any time I encounter it. Truly amazing.
Thanks for the great tip!

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[GlennUK]

Hi Scott,

I'm glad that it's going to be so useful for you.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Scott24x7]

Glenn,
Just an update some days on now... I have implemented this in 4 different sheets I have. It is an amazing "Cross Check", that helps to examine and identify where things have either been "missed", or where data does not match (has changed). This has been both a great time saver, and a great "proactive check" for me. Really, a very useful capability.
Much appreciated.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[GlennUK]

Hi Scott,

thanks for the feedback. Glad you've gotten so much usefulness from this.

Cheers, Glenn.

Beauty is in the eye of the beerholder.