General Topics

The Better vLookUp

by Brenda | November 20, 2018

Spreadsheet mastery comes in varying degrees, but there is one formula that seems to separate basic users from more advanced users. That formula is vLookUp.
To the advanced users, vLookUp is child’s play. Even the intermediate user thinks vLookUp is a basic tool to be used without much thought. However, apprentice users are often intimidated and confused by this formula.
If vLookUp challenges your Excel abilities, you might consider reading (or watching) a few vLookUp tutorials first. Then come back to this post with the confidence of an Excel Guru.

   THE PROBLEM WITH vLOOKUP

If your dataset is arranged with the lookup value on the left and sorted correctly with your lookup range all in one table, vLookUp might work fine. That is… until a column gets added, deleted, or moved. Now the return value column number has changed. There are also some limitations if you are trying to use the approximate match feature (but I rarely use that anyway).
If you use vLookUp frequently (or its sister formulas lookup and hlookup), but are frustrated by its limitations, you should consider ditching this underpowered tool for the new and improved Binford 6100 IndexMatch formula.

   BINFORD 6100 INDEXMATCH FORMULA

IndexMatch might be slightly more complicated, but it’s significantly more useful. Now I find myself using IndexMatch even when vLookUp would work fine. But I like my formulas like I like my trucks and chainsaws: powerful.
IndexMatch­, as you guessed, is two formulas, one embedded in the other. To combine these formulas to search like you would with vLookUp is quite easy. Once you get the hang of it, there are plenty of additional ways to use IndexMatch, but here are the basics.
The Index formula has three arguments, but you only need the first one, the array. This is the column containing the result you want to return. It doesn’t need to be the left column, it can be anywhere in any dataset.
The next argument is the row number, which gets replaced with the Match formula. The Match formula has three arguments. The first is the lookup value, which is the information you already know about what you are searching for, like the customer’s name, the date, or the order number. It could be a number, text, or (most likely) a cell reference.
The next argument is the lookup array, which is the column containing your lookup value.
And the last argument is the Match Type. Use -1 for greater than searches, 0 for exact searches, and 1 for less than searches. Or, you can leave it out altogether and it will default to an exact match search.
Look at the following screenshot of a list of loans. If I know the payment amount (in cell L4) but need to find the date, I can use IndexMatch (in cell L5).
The next screenshot illustrates another common use of IndexMatch. I have a list of loans in cells K4:K7 in one file, but I need to know the loan terms from another file. I can use the IndexMatch in L4 and drag it down to L7, giving me the correct loan term for each corresponding loan number.
So next time you find yourself needing to use a lookup function in your spreadsheet, consider using the tool the pros use, the IndexMatch. Not only will it work better, but it is sure to impress as well.

Home Improvement

Hours of Operation Monday – Friday
8am – 6pm MT
Saturday
9am – 4pm MT

Holiday Closures

Please note: Holiday closures are not the same for the Recreation and Home Improvement Departments.

  • New Year’s Day
  • Labor Day
  • Memorial Day
  • Thanksgiving
  • Independence Day
  • Christmas
  • New Year’s Day
  • Memorial Day
  • Independence Day
  • Labor Day
  • Thanksgiving
  • Christmas
  • Day before Thanksgiving,
    Wednesday,
    November 26, 2025
    – Closing at 3:00 MT

    Day after Thanksgiving,
    Friday,
    November 28, 2025
    – Closing at 4:00 MT
  • Day before Christmas,
    Wednesday,
    December 24, 2025
    – Closing at 3:00 MT

    Day After Christmas,
    Friday,
    December 26, 2025
    – Closing at 4:00 MT

    Saturday after Christmas,
    December 27, 2025
    – Closed
  • New Year’s Eve
    Wednesday,
    December 31, 2025
    – Closing at 4:00 MT

    Day After New Years,
    Friday,
    January 2, 2026
    – Closing at 4:00 MT

    Saturday After New Years,
    January 3, 2026
    – Closed

Home Improvement Application Processing Open as Follows

Home Improvement loan application processing is open on the following holidays. All other departments are closed.

  • Martin Luther King Jr. Day
  • Columbus Day/Indigenous Peoples’ Day
  • Presidents’ Day
  • Veterans Day
  • Juneteenth
  • Martin Luther King Jr. Day
  • Presidents’ Day
  • Juneteenth
  • Columbus Day/Indigenous Peoples’ Day
  • Veterans Day

Recreation

Hours of Operation Monday – Friday
8am – 5pm MT
Closed Weekends

Holiday Closures

  • New Year’s Day
  • Labor Day
  • Martin Luther King Jr. Day
  • Columbus/Indigenous Peoples’ Day
  • Presidents' Day
  • Veterans Day
  • Memorial Day
  • Thanksgiving
  • Juneteenth
  • Christmas Day
  • Independence Day
  • New Year’s Day
  • Martin Luther King Jr. Day
  • Presidents' Day
  • Memorial Day
  • Juneteenth
  • Independence Day
  • Labor Day
  • Columbus/Indigenous Peoples’ Day
  • Veterans Day
  • Thanksgiving
  • Christmas Day
  • Day before Thanksgiving,
    Wednesday,
    November 26, 2025
    – Closing at 3:00 MT

    Day after Thanksgiving,
    Friday,
    November 28, 2025
    – Closing at 4:00 MT

    Day before Christmas,
    Wednesday,
    December 24, 2025
    – Closing at 3:00 MT
  • Day After Christmas,
    Friday,
    December 26, 2025
    – Closing at 4:00 MT

    New Year’s Eve
    Wednesday,
    December 31, 2025
    – Closing at 4:00 MT

    Day After New Years,
    Friday,
    January 2, 2026
    – Closing at 4:00 MT

Borrower Services

Hours of Operation Monday - Friday
8am-11pm MT
Saturday
9am-1pm MT

Holiday Closures

:
  • New Year's Day
  • Thanksgiving
  • Memorial Day
  • Christmas
  • Independence Day
  • New Year's Day
  • Memorial Day
  • Independence Day
  • Thanksgiving
  • Christmas

Welcome to Our New Website

Welcome to the newly redesigned MedallionBank.com. Our website now features a fresh look and includes a dedicated FAQ page for borrowers, as well as new content for strategic partners.

We would like to hear from you.

We value your feedback and always want to learn how we can improve. Please submit thoughts about your experience with Medallion Bank here:

Submit Feedback

Give us a review

We invite you to share your experience with Medallion Bank on one of the websites listed below. Your reviews and feedback are important to us!