vlookup substitute

[bdmichael09]

So I had been trying to use vlookup to return a value from a table. My problem is that the table doesn't have constant values, there are jumps in the criteria, so vlookup is failing me. After trying to search the forums and other websites its kind of seeming like I need to use some sort of index or match function but I can't quite get it to work. Below is an example of what I'm working with. If I wanted to search for the value 22, I want the return value to be 200. Any idea how to accomplish this?

[TABLE="width: 128"]
<tbody>[TR]
[TD="colspan: 2"]A B



[/TD]
[/TR]
[TR]
[TD="colspan: 2"] 20 100[/TD]
[/TR]
[TR]
[TD="colspan: 2"] 40 200[/TD]
[/TR]
[TR]
[TD="colspan: 2"] 60 300[/TD]
[/TR]
[TR]
[TD="colspan: 2"] 80 400[/TD]
[/TR]
[TR]
[TD="colspan: 2"]140 500[/TD]
[/TR]
[TR]
[TD="colspan: 2"]190 600[/TD]
[/TR]
[TR]
[TD="colspan: 2"]240 700 [/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]

 

[Roxxien]

Hello,

Yeah, index/match can be a good idea for you.

Match is to find the request value in a list are the one closer to it. Your array for match need to be ONE column or ONE row if you don't want an error.

=Match(22,A1:A7,#)

Match have 3 option for the # argument : -1,0,1

0 stand for exact value, -1 stand for less then and 1 stand for greater then. In your case, it seem that 1 would be a good idea for what you want.

Index on it's side return the value of a specific row/column number.

=Index(A1:B7,3,2) would return 300.

So, in global this is what you need.

=Index(A1:B7,Match(22,A1:A7,1),2)

 

[Jonmo1]

If you want to return the next highest value, then standard index/match won't work either, at least not with your data sorted the way it is.

If you can sort the data in DESCENDING order 240 190 140 80 60 40 20
Then you can use -1 as the match type

=Index(B1:B7,Match(22,A1:A7,-1))

 

[bdmichael09]

While I can get the little example I used before to work, the actual larger project I am trying to work on I can't seem to get to function how I want it to. I'm trying to get the code to evaluate the run scores in the table (at the bottom of the page). I'm using the following code:

For Each cell In genderrange 'for everyone
gender = cell.Value 'gender value
age = cell.Offset(0, 1).Value 'how old
twomileraw = cell.Offset(, 6).Value 'two mile raw score in total seconds
If twomileraw <= 774 Then 'if soldier ran two miles in 774 seconds or less
twomilescore = 100 'this is the score to use
    ElseIf gender = "m" Then 'otherwise, is the soldier male
        Select Case age 'male = true, how old is the soldier
            Case Is >= 62
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("u5:u141"), 1), 20)
            Case Is >= 57
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("s5:s141"), 1), 18)
            Case Is >= 52
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("q5:q141"), 1), 16)
            Case Is >= 47
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("o5:o141"), 1), 14)
            Case Is >= 42
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("m5:m141"), 1), 12)
            Case Is >= 37
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("k5:k141"), 1), 10)
            Case Is >= 32
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("i5:i141"), 1), 8)
            Case Is >= 27
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("g5:g141"), 1), 6)
            Case Is >= 22
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("e5:e141"), 1), 4)
            Case Is >= 17
                twomilescore = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("c5:c141"), 1), 2)
            End Select
            cell.Offset(, 9) = twomilescore
    ElseIf gender = "f" Then 'is the soldier female
        Select Case age 'female = true, how old is the soldier
            Case Is >= 62
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("v5:v141"), 1), 21)
            Case Is >= 57
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("t5:t141"), 1), 19)
            Case Is >= 52
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("r5:r141"), 1), 17)
            Case Is >= 47
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("p5:p141"), 1), 15)
            Case Is >= 42
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("n5:n141"), 1), 13)
            Case Is >= 37
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("l5:l141"), 1), 11)
            Case Is >= 32
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("j5:j141"), 1), 9)
            Case Is >= 27
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("h5:h141"), 1), 7)
            Case Is >= 22
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("f5:f141"), 1), 5)
            Case Is >= 17
                twomileraw = Application.WorksheetFunction.Index(runchart.Range("b5:v141"), Application.Match(twomileraw, runchart.Range("d5:d141"), 1), 3)
            End Select
            cell.Offset(, 9) = twomilescore
    End If
cell.Offset(, 9) = twomilescore
Next cell
For Each cell In genderrange
gender = cell.Value
pushupscore = cell.Offset(, 7)
situpscore = cell.Offset(, 8)
twomilescore = cell.Offset(, 9)
totalscore = pushupscore + situpscore + twomilescore 'the soldiers total score is the sum of the three event scores
cell.Offset(, 10) = totalscore 'put the total score in this cell
Next cell

And referencing the following table (the actual table is much larger, this is just a small bit)

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl75, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "]AGE GROUP
[/TD]
[TD="class: xl71, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "][/TD]
[TD="class: xl71, width: 128, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] , colspan: 2"]17-21
[/TD]
[TD="class: xl71, width: 128, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] , colspan: 2"]22-26
[/TD]
[/TR]
[TR]
[TD="class: xl72, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "][/TD]
[/TR]
[TR]
[TD="class: xl75, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "]Time
[/TD]
[TD="class: xl75, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "]Total Seconds
[/TD]
[TD="class: xl75, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "]M
[/TD]
[TD="class: xl75, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "]F
[/TD]
[TD="class: xl75, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "]M
[/TD]
[TD="class: xl75, width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] "]F
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]20:12
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1212
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]1
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]44
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]20
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]54
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]20:06
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1206
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]2
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]45
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]21
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]55
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]20:00
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1200
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]3
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]47
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]22
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]56
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:54
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1194
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]5
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]48
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]23
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]57
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:48
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1188
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]6
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]49
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]24
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]58
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: yellow"]19:42
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1182
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]8
[/TD]
[TD="class: xl69, width: 64, bgcolor: yellow"]50
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]26
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]59
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:36
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1176
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]9
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]52
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]27
[/TD]
[TD="class: xl70, width: 64, bgcolor: #CCCCCC"]60
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:30
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1170
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]10
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]53
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]28
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]61
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:24
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1164
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]12
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]54
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]29
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]62
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:18
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1158
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]13
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]55
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]30
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]63
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:12
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1152
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]14
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]56
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]31
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]64
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:06
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1146
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]16
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]58
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]32
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]65
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]19:00
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]1140
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]17
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]59
[/TD]
[TD="class: xl68, width: 64, bgcolor: silver"]33
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]66
[/TD]
[/TR]
</tbody>[/TABLE]

I get the following output


[TABLE="width: 832"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 256, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , colspan: 4"]Raw Scores
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 256, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , colspan: 4"]Scores
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Name
[/TD]
[TD="class: xl65, bgcolor: transparent"]SSN
[/TD]
[TD="class: xl65, bgcolor: transparent"]Gender
[/TD]
[TD="class: xl65, bgcolor: transparent"]Age
[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]Push Up
[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]Sit Up
[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]2 Mile Min
[/TD]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]2 Mile Sec
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Push Up
[/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Sit Up
[/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]2 Mile
[/TD]
[TD="class: xl67, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Total
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]m
[/TD]
[TD="bgcolor: transparent, align: right"]19
[/TD]
[TD="bgcolor: transparent, align: right"]80
[/TD]
[TD="bgcolor: transparent, align: right"]90
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]720
[/TD]
[TD="bgcolor: transparent, align: right"]100
[/TD]
[TD="bgcolor: transparent, align: right"]100
[/TD]
[TD="bgcolor: transparent, align: right"]100
[/TD]
[TD="bgcolor: transparent, align: right"]300
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"]m
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]35
[/TD]
[TD="bgcolor: transparent, align: right"]41
[/TD]
[TD="bgcolor: transparent, align: right"]19
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]1143
[/TD]
[TD="bgcolor: transparent, align: right"]54
[/TD]
[TD="bgcolor: transparent, align: right"]48
[/TD]
[TD="bgcolor: transparent, align: right"]62
[/TD]
[TD="bgcolor: transparent, align: right"]164
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"]f
[/TD]
[TD="bgcolor: transparent, align: right"]19
[/TD]
[TD="bgcolor: transparent, align: right"]50
[/TD]
[TD="bgcolor: transparent, align: right"]64
[/TD]
[TD="bgcolor: transparent, align: right"]19
[/TD]
[TD="bgcolor: transparent, align: right"]28
[/TD]
[TD="bgcolor: transparent, align: right"]1168
[/TD]
[TD="bgcolor: transparent, align: right"]100
[/TD]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]62
[/TD]
[TD="bgcolor: transparent, align: right"]240
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"]f
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]1212
[/TD]
[TD="bgcolor: transparent, align: right"]50
[/TD]
[TD="bgcolor: transparent, align: right"]47
[/TD]
[TD="bgcolor: transparent, align: right"]62
[/TD]
[TD="bgcolor: transparent, align: right"]159
[/TD]
[/TR]
</tbody>[/TABLE]

Again it is the 2 mile scores I'm interested in. The first person works because he skips the entire Index match function. The rest however give not just the wrong value but they duplicate. Any ideas? I'm at my wits end.