Lookup function referencing a table with multiple rows and columns

if you can use table 2, it will be a simple vlookup:
=VLOOKUP(G7,C18:D33,2,FALSE)

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

Please see attached files.
One contains solution using macro and another using VLookUp

LookupChallenge-Using-Macro.xls
LookupChallenge-Using-VLookUp.xls

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.

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

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

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

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

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

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

Select allOpen in new window

LookupChallenge-02.xls