VLOOKUP within number range

[RobsterM]

Hello,

I use VLOOKUPs quite regularly but it seems it doesn't like to reference a range of numbers just a single number.

In the example below, I want to return the four-letter codes in column A where the corresponding percentages in column B range between 6-7% with the codes going into column C. Specifically, C1 would have DFTW, C2 LPPI and C3 would have QWQW

My data set is around 50,000 rows

AGUY (cell A1) 5.590%
DFTW . 6.2556%
LPPI . 6.373%
DREW . 9.667%
TQRR . 4.204%
YURD . 8.817%
QWQW . 6.720%

Any ideas?

 

[AhoyNC]

Maybe:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Change cells to match your data.
Copy formula in C1 down as needed.

Excel Workbook
	A	B	C	D
1	AGUY	5.59%	DFTW
2	DFTW	6.26%	LPPI
3	LPPI	6.37%	QWQW
4	DREW	9.67%
5	TQRR	4.20%
6	YURD	8.82%
7	QWQW	6.72%
8
Sheet

 

[bosco_yip]

Assume data in range A1:B7

1] C1, enter array formula and copy down :

=INDEX(A:A,SMALL(IF((B$1:B$7>=0.06)*(B$1:B$7<=0.07),ROW($1:$7),1000),ROWS($A1:A$1)))&""

Confirmed enter with Shift+Ctrl+Enter in stead of Enter

or,

2] this non array formula, in C1 copy down :

=LOOKUP(ROW(A1),COUNTIFS(OFFSET($B$1,,,ROW($1:$8)-1),">=0.06",OFFSET($B$1,,,ROW($1:$8)-1),"<=0.07")+1,A$1:A$8)&""

Regards

 

[Trebor76]

I use VLOOKUPs quite regularly but it seems it doesn't like to reference a range of numbers just a single number.

You need a left lookup which cannot be performed via a VLOOKUP. As well as the two nifty formula provided above here's a macro solution as well (best of both worlds I suppose):

Option Explicit
Sub Macro2()

    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim blnIncrementRow As Boolean

    lngLastRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    For lngMyRow = 1 To lngLastRow
        If Range("B" & lngMyRow).Value >= 0.06 And Range("B" & lngMyRow).Value <= 0.07 Then
            'If the match is the first match, then...
            If blnIncrementRow = False Then
                '...manually put the entry from Col. A into cell C1.
                Range("C1").Value = Range("A" & lngMyRow).Value
            'Else...
            Else
                '...put the entry from Col. A into the next available row of Col. C.
                Range("C" & Cells(Rows.Count, "C").End(xlUp).Row + 1).Value = Range("A" & lngMyRow).Value
            End If
            blnIncrementRow = True
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
    MsgBox "Process is complete."

End Sub

Regards,

Robert