[Solved] Formula Help

Discuss the spreadsheet application

[Solved] Formula Help

Postby lcwilcox » Sat Mar 23, 2019 3:49 am

Hi there! I've been working all day on this, and I can't find a solution. I have a set of student information and their IDs. (Columns A, B, and C in the example). I have the student IDs and reading numbers from another program (Columns D and F in the example). I have already worked on a formula in Column E to match the ID in D with the ID in C, and if they are the same in the same row, then to concatenate the student's first and last name. However, you'll see that in Column D, the ID doesn't always match up in the row, since the student submitted more than one reading slip.

I want to add to the formula for Column E so that if the ID in Column D doesn't equal Column in C, then the program should search Column C for the matching ID, and then identify the student's first and last name in Column E.

So, If you see in the example, E5 is "False" because the ID in D5 doesn't match the ID in C5. Instead, it matches the one in C4, since the student entered a slip two times. I would like to program it so that it marks D5 as Adam Thomas, since his ID matches Adam's.

Does this make sense?

Thanks for any help!
Attachments
Roster Example.ods
(30.01 KiB) Downloaded 6 times
Last edited by robleyd on Sat Mar 23, 2019 5:00 am, edited 3 times in total.
Reason: Add green tick
OpenOffice 4.1.0 on MacOS 10.13.6
lcwilcox
 
Posts: 4
Joined: Sat Mar 23, 2019 3:22 am

Re: Formula Help

Postby FJCC » Sat Mar 23, 2019 4:12 am

I made a lookup table in columns A and B and used a VLOOKUP() function in column G to match the ID in F with the correct name. You can hide columns A and B if you want to.
Attachments
Roster_fjcc.ods
(16.11 KiB) Downloaded 10 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7302
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula Help

Postby lcwilcox » Sat Mar 23, 2019 4:17 am

That's exactly what I needed. Thank you! If you have a moment, could you explain what a lookup table is and why it's needed? I tried the VLOOKUP function before, but I didn't understand it, despite my research.

Thanks again!
OpenOffice 4.1.0 on MacOS 10.13.6
lcwilcox
 
Posts: 4
Joined: Sat Mar 23, 2019 3:22 am

Re: [Solved] Formula Help

Postby FJCC » Sat Mar 23, 2019 4:44 am

All I mean by a lookup table is that the thing you are looking up, the ID in this case, is in the leftmost column of the range and the thing you want to read back is in a single column. So I put the IDs in A and the full name in B. These are not hard requirements in all cases, the LOOKUP() function is more flexible in some ways than VLOOKUP(). Also, I could have used VLOOKUP twice, once to lookup the first name and again for the last name, and concatenated those two results. The solution I used seems cleaner and easier to maintain.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7302
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Formula Help

Postby lcwilcox » Sat Mar 23, 2019 4:26 pm

Thank you so much for your help. It saved me a lot of time.
OpenOffice 4.1.0 on MacOS 10.13.6
lcwilcox
 
Posts: 4
Joined: Sat Mar 23, 2019 3:22 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 27 guests