• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

Lookup function referencing a table with multiple rows and columns

I doubt that that my question title really describes the situation, but please take a look at the attached workbook. Basically I have a table with 4 columns, each containing 2 to 6 rows of data under the header.
The table data comprises 16 items that will be distributed over a 2500 row column elsewhere in the workbook. I needa function (ideally) or a macro such that when one of the data values in the table appears in the long target column, it will return the header value of whatever column it resides in the table.

I'm sure my explanation here isn't very clear either, but I hope the workbook will make things clear!

Thanks,
John
LookupChallenge.xls
0
gabrielPennyback
Asked:
gabrielPennyback
  • 4
  • 2
  • 2
  • +3
1 Solution
 
GuruChiuCommented:
if you can use table 2, it will be a simple vlookup:
=VLOOKUP(G7,C18:D33,2,FALSE)
0
 
yuppyduCommented:
not having the values in c18:c33 in ascending order the best thing is to use vlookup in VBA which does not need the values to be in order
0
 
VipulKadiaCommented:
Please see attached files.
One contains solution using macro and another using VLookUp

LookupChallenge-Using-Macro.xls
LookupChallenge-Using-VLookUp.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
yuppyduCommented:
try this, is very simple.
name cell h7 as start
name c18:c33 as table
in any cell put this formula: counta(g7:g33) this will give you the number of entries you have to lookup
open vba and rename sheet1 as wksDataSheet
open a new module and copy this code:

Dim iCounter As Integer
Sub Fill_range()

For iCounter = 0 To Range("numberofentries").Value - 1
With wksDataSheet.Range("start")
.Offset(iCounter, 0) = Application.WorksheetFunction.VLookup(wksDataSheet.Range("start").Offset(iCounter, -1), wksDataSheet.Range("table").Resize(, 2), 2, False)
End With
Next iCounter

End Sub

It works, just tested. This way you do not have to have the values in ascending orders.
0
 
patrickabCommented:
John,

That's one of the easiest challenges you have given us. The code below is in the attached file. The usage is like an ordinary Excel formula:

=finder(G7)

where G7 is the cell containing the string you want to find in the yellow-celled range B8:E13 The formula can be copied or moved just the same as an ordinary formula.

Patrick


Function finder(ByVal rng As Range) As String
Dim rng_table As Range
Dim rng_found As Range

With Worksheets("Sheet1").Range("B8:E13")
    Set rng_found = .Find(rng, LookIn:=xlValues)
    finder = .Cells(7, rng_found.Column - 1)
End With

End Function

Open in new window

LookupChallenge-01.xls
0
 
patrickabCommented:
John,

It doesn't change the function but I had left a superfluous line of code. Without that is becomes:

=========
Function finder(ByVal rng As Range) As String
Dim rng_found As Range

With Worksheets("Sheet1").Range("B8:E13")
    Set rng_found = .Find(rng, LookIn:=xlValues)
    finder = .Cells(7, rng_found.Column - 1)
End With

End Function
==========

Patrick
0
 
barry houdiniCommented:
Hello John,
If you want to use Table 1 rather than table 2 then you can use this formula in H7
=IF(COUNTIF(B$8:E$13,G7),INDEX(B$7:E$7,MIN(IF(B$8:E$13=G7,COLUMN(B$8:E$13)-COLUMN(B$8)+1))),"No Match")
That's an array formula that needs to be confirmed with CTRL+SHIFT+ENTER and then copied down the column, see attached
regards, barry
 
 

24998808.xls
0
 
gabrielPennybackAuthor Commented:
Thanks, Patrick. Barry, you were the only other one who got that I wanted to avoid having to recreate the existing table, but I chose Patrick's answer in order to avoid the array formulas, which in my experience will slow things down quite a bit with 2,500 cells to be processed.
Thanks to all of you and I hope you all have a very Merry Christmas or whatever you might celebrate over the holidays!
John
0
 
gabrielPennybackAuthor Commented:
Patrick, I'm glad I finally came up with something easy for you, but that will still make a big difference for me. Thanks for the early Christmas present.

- John
0
 
patrickabCommented:
John,

Thanks for the grade.

BTW if the vaues don't change then I suggest that I either re-write the macro so that it only leaves a value in the cells or if you want to convert the formulae to values just copy and paste-special-values back into the same range. That will then ensure that there are no formulae to slow down your workbook.

Let me know if you want a macro re-write to do that.

Patrick

0
 
patrickabCommented:
John,

The straight macro is below and in the attached file. Just press the button to get the results. It only leaves the values - should speed up your file a bit as it doesn't leave formulae in the cells.
Patrick
Sub finder2()
Dim rng As Range
Dim rng2 As Range
Dim celle As Range
Dim rng_found As Range

With Sheets("Sheet1")
    Set rng = Range(.Cells(7, "G"), .Cells(.Rows.Count, "G").End(xlUp))
    Set rng2 = .Range("B8:E13")
    For Each celle In rng
        Set rng_found = rng2.Find(celle, LookIn:=xlValues)
        celle.Offset(0, 1) = .Cells(7, rng_found.Column)
    Next celle
End With

End Sub

Open in new window

LookupChallenge-02.xls
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now