# How VLOOKUP to left but can't use MATCH

#### somanyqs

##### Board Regular
I see lots of answers on how to use MATCH (and INDEX) to essentially turn a vlookup into a vlookup but to the cells on the *left*, but can't find one without.

What I'm trying to to do is look up a dollar amount within column D (dollar amounts in D are in order, increasing), then if it finds the dollar amount, return the value in column A in the same row.

If it doesn't find the dollar amount (which it generally won't), I would like it to find the next highest dollar amount in column D, and then return the value in the same row of column A.

I've read lots to use an INDEX(MATCH), but that will only work if it will always find a match for me, which it virtually never will. I'm sure I'm missing something easy here...any ideas?

Thanks all!

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I'm not sure if I understood you right but here goes.

For my example i put the "Names" in cells A3 on wards and the dollar numbers in D3 on wards.

Next, i put the lookup amount in cell F1

The formula i entered in:

F3 = "=INDEX(A3:D6,MATCH(F1,\$D\$3:\$D\$6,1),1)"

Last edited:
Maybe something like this.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 55px"><COL style="WIDTH: 44px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Data</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">Dollar</TD><TD> </TD><TD style="FONT-WEIGHT: bold">Find</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">\$ 1.00 </TD><TD> </TD><TD style="TEXT-ALIGN: right">\$ 4.01 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">\$ 2.00 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">\$ 3.00 </TD><TD> </TD><TD>Data</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">7</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">\$ 4.00 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">8</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">\$ 5.00 </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">9</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">\$ 6.00 </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G4</TD><TD>=IF(ISNA(MATCH(F2,D2:D7,0)),INDEX(A2:A7,MATCH(F2,D2:D7,1)+1),INDEX(A2:A7,MATCH(F2,D2:D7,0)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
<!-- / message --><!-- sig -->

!
Why didn't I think of using an IF with an ISNA!!
(oh, because I'm not this advanced...)

Thanks much, really!!

Replies
6
Views
559
Replies
6
Views
218
Replies
3
Views
159
Replies
1
Views
399
Replies
3
Views
356

1,202,922
Messages
6,052,575
Members
444,593
Latest member
Smaxls

### 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.

### Which adblocker are you using?

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

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