Results 1 to 4 of 4

Thread: Picking a row from a range

  1. #1

    Picking a row from a range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    forum attachment.xlsx
    I have a list of existing sizes L2:M50

    I have a user input form: B2 and B3 (length, B4 doesn't pertain)

    I want to be able to have a user enter dimensions in B2 and B3 and have the spreadsheet tell them whether that size exists in the data range L2:M50. This is a very small portion of the data range(for demonstration only). The full data range is 500 rows and growing.

    I've tried VLOOKUP, and INDEX with MATCH, but haven't got it. I've also looked at TABLE.
    Running EXCEL 2013

    Any suggestions would be greatly appreciated, and thank you in advance.

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi smithrv. Here's how I'd do it:

    First, turn your lookup table into an Excel table. I use the keyboard shortcut Ctrl + T to do this.
    Then in say cell C6 put "Number of matches"
    Then in say cell C7 enter this formula:
    =COUNTIFS(Table1[OD],B2,Table1[ID],B3)

    By the way, calculation is set to manual in your file. I never do this...it's too risky.

    Many people mistakenly think that having to run a spreadsheet in manual calculation mode is simply a byproduct of having a big spreadsheet with lots of data and formulas in it. But in actual fact in most cases I've seen, performance issues are more often than not because of inefficient spreadsheet design compounded by so-called volatile functions such as OFFSET, INDIRECT, TODAY, NOW, and RAND rather than simply being a by-product of big files.

  3. #3
    Outstanding, thanks so much Jeffrey. So funny, I was going to search my Excel books for countif tomorrow. Calculation set to manual-I just use default, thanks for the tip. I will have a another table, in the near future, that I can use this same formula on. Same format, different data.

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    No problem. Thanks for the feedback, and see you back here anytime :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •