If/Vlookup Help

Slythe

New Member
Joined
Jan 16, 2005
Messages
10
Hi,

I have created a table with info i need in and called it BusNumber.
the 3rd column along is the amount of seats the bus has on it.
In Cell b7 i input an number, lets say 20 for example, then below that in cells b17 downwards i would like it to come up with a list of busses that have that number of seats and more, with my limited knowledge of spreadsheets as far i was thinking a formula along these lines would work

=If(Vlookup(b7,BusNumber,3,>b7),(vlookup(b7,BusNumber,2)),"")

and if there werent any busses that met the criteria it would remain blank, however this has not worked on my sheet, it says there's an error. I think that this could need a macro but i'm not sure.

I'd be very greatful of any help
Regards Si
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
There are macros and formula sets which will do what you want, but I'm thinking you might be best off filtering the data with a Custom criteria of Greater than/Equal to [ your # seats ].

Sound like a plan?
 

Slythe

New Member
Joined
Jan 16, 2005
Messages
10
I dont want to sound stupid or anything, but how do i go about doing that?
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Go to your data table, and highlight the entire column containing your bus seat numbers by clicking on the column letter.

Then click on Data | Filter | AutoFilter

You'll see a down-arrow in your 1st cell -- click on it.

Select Custom, and from the box which contains "Equal" select "is greater than or equal to"

Enter your seat number in the empty box to the right.

Click OK.

What should be left are only those rows meeting your criteria.

You can get everything back by clicking the down-arrow and selecting All.

You can remove the filter entirely by clicking Data hen unchecking Autofilter.
 

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
I modified a popular look up UDF (user defined function) called VLOOKUPNTH to meet your needs if you want to try it.

Here's the code.

Code:
Function VLOOKUPBACKNTH(lookup_value, table_array As Range, _
            nth_value)
' Extension to VLOOKUP function.  Allows for finding
' the    "   nth   "    item that matches or is greater than
' the lookup value.

Dim nRow As Long
Dim nVal As Integer
Dim nCol As Long

  VLOOKUPBACKNTH = ""
  
  With table_array
    nCol = .Columns.Count
    For nRow = 1 To .Rows.Count
      If .Cells(nRow, nCol).Value >= lookup_value Then
        nVal = nVal + 1
      End If
      If nVal = nth_value Then
        VLOOKUPBACKNTH = .Cells(nRow, 1).Value
        Exit Function
      End If
    Next nRow
  End With
  
End Function

To add this function to your current workbook...

Press [alt][F11]

In the PROJECT - VBA PROJECT window on the left, find the name of your workbook. ex: VBAProject (BusNumber.xls) and click on it.

From the drop down menus goto INSERT and click on MODULE.

If you click on the + sign next to your project you can see a new folder called MODULES and under that a module called MODULE1.

Double click MODULE1 and paste the code into that module window on the right side of the screen.

Now you're ready to use the Function (UDF) like a regular formula.
=VLOOKUPBACKNTH(lookup_value, table_array As Range, _
nth_value)

lookup_value = 20 or the cell you enter a seat number. In your example its B7.
Table_array is your table "BusNumber".
nth_value is the steps of the lookup. 1st value, 2nd value, etc.

Enter this in B17
=VLOOKUPBACKNTH($B$7,Busnumber,ROW(A1))

and copy it down. You'll get all the busses that have 20+ seats.

Example:
Book1
ABCDE
1BusSeatsSeatsBusnumber
2121201
32202
43233
5447
6559
7610 
8740 
9819 
10920 
111018 
Sheet1
 

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
Since you specified a TABLE, let me note that this function works if your far left column of the table has the BUS NUMBER and the far right column houses the SEATS.

If you are typing in a range:
This function also only will work if data you are trying to retrieve is always in the far left column of your range and your lookup row is in your far right column of the range. Make sure you use absolute references when specifying the range and your lookup value: ie: $$
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

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
Top