How VLOOKUP to left but can't use MATCH

[somanyqs]

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!

 

[Mindpsyche]

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)"

 

[AhoyNC]

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

 

[somanyqs]

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

Thanks much, really!!