I need help trying to come up with a formula that will allow me to input two pieces of data and then update a box with a number. In the picture attached it shows an example of what I need. If I input John & 3, it should return Blue, or Billy & 2 should return Yellow. Any help would be greatly appreciated

enter image description here


You can use the VLOOKUP function to do this.

Let's assume that you have entered the name ("John") in cell A1, the number (3) in cell B1 and the table from the question in the range A2:E6.


The formula looks up the contents of cell A1 ("John") in the first column of the range A2:E6 (where the names are) and returns the contents of the nth column of that row of the table (where n is one more than the contents of cell B1).

| improve this answer | |
  • Nice, that helps a bit but say I need to put Alan in A7 and 4 in A8 and on A9 have it spit out Blue. Would that still be using the Vlookup formula? – Skorrm Nov 2 '17 at 14:37
  • @Skorrm Yes. In the formula I wrote, replace A1 with the cell where you are entering the name to be looked up, replace B1 with the cell where you are entering the number (but leave the +1), and replace A2:E6 with the range that includes your table. – Blackwood Nov 2 '17 at 14:53
  • Awesome! That worked perfectly thank you so much! – Skorrm Nov 2 '17 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.