vlookup substitute

bdmichael09

New Member
Joined
Jul 17, 2017
Messages
9
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]
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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)
 
Upvote 0
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))
 
Last edited:
Upvote 0
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:

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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top