Table Lookup - 2 Input Values

  • I'm having a heck of a time figuring out how to perform this action in Excel. I have some understanding of VLOOKUP, but not enough to really do this properly. I've attached an example sheet so you can see what I'm talking about.


    The two yellow cells, B3 and B4, require user input:


    B3 can be a number from 0 to 800
    B4 can be a number from 0 to 300


    Based on the inputs in these two cells, I need to return the result of the lookup into cell B6 - Loss Experience.


    Thanks in advance for your help.

  • Re: Table Lookup - 2 Input Values


    Quote from EgoProwler

    The two yellow cells, B3 and B4, require user input:


    B3 can be a number from 0 to 800
    B4 can be a number from 0 to 300

    You might consider using data validation on these two cells to restrict to the ranges you indicated. You can also require that the number be a whole number, for example, 10 would be valid but 10.1 would not.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Table Lookup - 2 Input Values


    Quote from Barbarr

    You might consider using data validation on these two cells to restrict to the ranges you indicated. You can also require that the number be a whole number, for example, 10 would be valid but 10.1 would not.


    The input cells in this example will always be a whole number.

  • Re: Table Lookup - 2 Input Values


    Quote from EgoProwler

    The input cells in this example will always be a whole number.


    Then it would be a good idea to set Data Validation to Allow Whole Number Between 0 and 800 on B3 and Between 0 and 300 on B4 to prevent errors on the index formula.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Table Lookup - 2 Input Values


    Quote from EgoProwler

    ...Thank you SOOO much. This is exactly why I love this forum :)


    You're very welcome! Glad I could help!


    Also, Barbara made a great point. You may want to use Data Validation to restrict the input values within your ranges. Otherwise, if 720 and 245 were entered as input values, the formula would return 0.55%, which I assume would be incorrect.

  • Re: Table Lookup - 2 Input Values


    Quote from Barbarr

    Then it would be a good idea to set Data Validation to Allow Whole Number Between 0 and 800 on B3 and Between 0 and 300 on B4 to prevent errors on the index formula.


    Excellent point. I've already implemented Data Validation in other data entry fields of this sheet and this one will get the same treatement.


    Thanks again guys! I'm sure I'll have more questions here very soon for some other features I need to add to this sheet.

  • Re: Table Lookup - 2 Input Values


    Quote from EgoProwler

    Thanks again guys! I'm sure I'll have more questions here very soon for some other features I need to add to this sheet.

    We'll take that as a promise (and not a threat :) ). Looking forward to helping you.


    Good job Domenic!

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!