## [Solved] Formula Help

### [Solved] Formula Help

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
Last edited by robleyd on Sat Mar 23, 2019 5:00 am, edited 3 times in total.
OpenOffice 4.1.0 on MacOS 10.13.6
lcwilcox

Posts: 4
Joined: Sat Mar 23, 2019 3:22 am

### Re: Formula Help

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
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7607
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Formula Help

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

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
FJCC
Moderator

Posts: 7607
Joined: Sat Nov 08, 2008 8:08 pm

### Re: [Solved] Formula Help

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