First of all, I am a complete novice at spreadsheets, so please explain things to me like I'm five years old. Secondly, I've already searched this throughout the internet but the only help seems to be related to entering data based on the value of one cell, not two.
I'm a language teacher and I'm using a tracker to keep a record of all the lessons that I teach. There are three important values:
- The course (Beginner, Elementary, Intermediate, Upper-Intermediate or Advanced). I'm entering this information in column C.
- The lesson number (1-20). Each course contains 20 lessons. So, for example, once students complete all 20 lessons in the Beginner course, they move on to the Elementary course. I'm entering this information in column D.
- The lesson title. So for example, Beginner lesson 1 is called 'Introduction to English', and Intermediate lesson 15 is called 'Making phone calls'. I'm entering this information in column E.
When I'm booked for a new lesson, I currently have to manually enter all this information. But I want to speed up the process so that I just have to enter the course name in column C and the lesson number in column D, and the third cell will automatically update in column E with the lesson title.
At the moment this tracker is one sheet. A table of all the levels, lesson numbers and lesson titles is on another sheet.
If anyone could help me accomplish this, I'd very much appreciate it!
CodePudding user response：
you can use VLOOKUP where you join C&D and look that up in your other sheet where you have that table (let's say it's in Sheet1!A:C where C=Sheet1!A, D=Sheet1!B and E=Sheet1!C) then you just use this in row 2:
=INDEX(IFNA(VLOOKUP(C2:C&"×"&D2:D, {Sheet1!A:A&"×"&Sheet1!B:B, Sheet1!C:C}, 2, 0)))
update:
=INDEX(IFNA(VLOOKUP(E2:E, 'UPDATED Lessons'!A2:J,
MATCH(D2:D, 'UPDATED Lessons'!A1:J1, ), )))
CodePudding user response：
@player0 helped me out with the sheet in the end with this very clever formula. Thanks so much!
=INDEX(IFNA(VLOOKUP(E2:E, 'UPDATED Lessons'!A2:J, MATCH(D2:D, 'UPDATED Lessons'!A1:J1, ), )))