0

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

1

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.

=VLOOKUP(A1,A2:E6,B1+1,FALSE)

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).

  • 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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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