On Sheet 2, I have a directory of students in columns of Last name, First name, Phone Number, email. On Sheet 1, when I type the Last Name, in a column, how can I get it Fill in the Sheet 1 columns for First name, Phone Number, email from Sheet 2 ?
Thank you for your help. I am going to plug these in to fit my scenario. They are not really students but US Navy ships, but I wanted to keep it pretty simple for folks. Every ship has a universal three letter identifier so there are no chance of duplicates (Example: USS JOHN F. KENNEDY is JFK, USS NEW YORK is NYK)
Was able to find some guidance that used the following formula:
IF(F3="","",INDEX(Ship Contacts::Contact, MATCH($F3,Ship Contacts::A)))
I am going to apply yours and see which is simpler.
Again, thank you for your support and help!!
msj
I like XLOOKUP, too. It always bothers me when I need to use IFERROR.
You can get rid of the zeros several ways. Here are four I am thinking of:
1) Use the method in you earlier post =IF(F3="","", XLOOKUP())
This way makes the most sense to people, I think, and is trouble free.
2) Append &"" to the end of the formula =XLOOKUP()&""
This is easy but it will turn all cells into text. If there are any numbers in that data that you plan on doing things with (such as summing up the tonnage of the selected ships or something like that), this might not be the best way because functions like SUM ignore text, even if the text looks like a number.
3) Prefill all blank cells in Column A of either the results table or data table (but not both) with a space character. That way XLOOKUP will either be looking for a space character when there are none or will be looking for a blank cell when there are none. I suggest doing it to the data table because it stays constant and there is less likelihood of deleting one.
4) Shorten the data table so it has no blank rows. Ultimately this is the easiest because there is no change to the formula. It is simple enough to add new rows when you need to and the formula will pick them up automatically.
Take a look at COUNTIFS.
If the instructor name is in column A, the count of AM's for that person would be
=COUNTIFS(Ship Simulator Schedule::E,A,Ship Simulator Schedule::C, "X")
You might like using the checkbox format instead of the X's so you can click on it vs having to type an X. Checkboxes are either TRUE or FALSE. The formula would then be
=COUNTIFS(Ship Simulator Schedule::E,A,Ship Simulator Schedule::C, TRUE)
Looking up by last name assumes no two students will have the same last name. I suppose you can try that but the example below looks them up by student ID, which will definitely be a unique value. I made it in two tables in the same sheet to make it easier to get a screenshot of it.
Student Directory is all typed in manually, no formulas.
Column A of Table 1 is typed in manually
Formula in cell B2 of Table 1 =IFERROR(VLOOKUP($A2,Student Directory::$A:$E,COLUMN(),0),"")
Fill to the right and down to the last row to complete the table.
You were asking about doing a lookup. There are several ways to do it. I used VLOOKUP, set for exact match only (will not return a "close match" if no exact match exists). I surrounded it with the IFERROR function because if VLOOKUP does not find the value being searched for, it will result in an error triangle. This would happen whenever there is no value in column A. IFERROR catches that error and put a null string "" into the cell instead of an error triangle.
If you have Numbers 10.x you can have a simpler formula by using XLOOKUP, which doesn't need IFERROR.
In B2 of 'First Match by Last' tabled filled or copied right into the other cells of that row:
=XLOOKUP($A2,Directory::$B,Directory::B,"",0)
In B2 of 'Lookup by ID' tabled filled or copied right into the other cells of that row:
=XLOOKUP($A2,Directory::$A,Directory::B,"",0)
SG
Thank you for your help. I am going to plug these in to fit my scenario. They are not really students but US Navy ships, but I wanted to keep it pretty simple for folks. Every ship has a universal three letter identifier so there are no chance of duplicates (Example: USS JOHN F. KENNEDY is JFK, USS NEW YORK is NYK)
Was able to find some guidance that used the following formula:
IF(F3="","",INDEX(Ship Contacts::Contact, MATCH($F3,Ship Contacts::A)))
I am going to apply yours and see which is simpler.
Again, thank you for your support and help!!
msj
INDEX(MATCH()) is another way to do searches. The formula you postedwill not throw an error on blank rows but has another potential problem, though. The MATCH function is not set to "exact match". If it does not find an exact match, it may return data for a "close match". That is, it may return information for a different ship. This can happen if the name you are searching for is not in the table or if you misspelled it.
The new XLOOKUP function may be the simplest.
The XLOOKUP function is totally new to me so I plan to redo my sheet with your guidance on Sunday; wine tonight, great weather tomorrow, rain on Sunday, so that’s a good time for me to concentrate on it.
I’m looking forward to making it work and expand some of my NUMBERS knowledge base. Again, thanks for your expertise and I’ll let you know how it goes.
msj
Badunit: I like the XLOOKUP as is it easier and shorter. Just as your example above, my formula ends with "exact match" The drop down choice is "exact match (0 or omitted)" It fills the cells below that are not yet in use (but will be later) with Zeros all the way down. Since the word "omitted" is there, how do I get it to leave the Return Range cell empty if I have not entered a search value in the search cell yet?
I like XLOOKUP, too. It always bothers me when I need to use IFERROR.
You can get rid of the zeros several ways. Here are four I am thinking of:
1) Use the method in you earlier post =IF(F3="","", XLOOKUP())
This way makes the most sense to people, I think, and is trouble free.
2) Append &"" to the end of the formula =XLOOKUP()&""
This is easy but it will turn all cells into text. If there are any numbers in that data that you plan on doing things with (such as summing up the tonnage of the selected ships or something like that), this might not be the best way because functions like SUM ignore text, even if the text looks like a number.
3) Prefill all blank cells in Column A of either the results table or data table (but not both) with a space character. That way XLOOKUP will either be looking for a space character when there are none or will be looking for a blank cell when there are none. I suggest doing it to the data table because it stays constant and there is less likelihood of deleting one.
4) Shorten the data table so it has no blank rows. Ultimately this is the easiest because there is no change to the formula. It is simple enough to add new rows when you need to and the formula will pick them up automatically.
I added the "IF" before "XLOOKUP" as you recommended and got the desired result. You have been a wonderful and patient tutor providing me Varsity Level support.
I want to expand my capability with Numbers and formulas. Can you recommend a particular avenue other than to come crying to you all the time? Thanks.
The help menu in Numbers has a lot of useful information on how to use Numbers and about formulas and functions. That would be the best place to start. You can always come here but no crying allowed. There are some really talented and knowledgable individuals here willing to help (XLOOKUP was SGIII's idea). A lot of questions have already been answered over a lot of years but finding them can be difficult, even if you know exactly what search words to use. I find it much more productive to search these forums using an Internet search vs the totally worthless (in my opinion, and I cannot stress my disappointment strongly enough without my keyboard catching fire) search bar provided here.
Right after I sent that email for you looking for sources, I found the numbers manual online and found it to be a very useful source. Also thanks to SGIII for the interest and support.
So I was all set with my schedule doing everything I wanted and then I had another idea! Below is my scheduling sheet. We have a total of five instructors and we make a new schedule each month. Instructors can work in the AM, PM, or Both. So as you can see below, I have columns where we can place an "X" as appropriate in Column C, D, or Both. We try to keep the assignments close to equal.
I got the idea if I made an additional sheet with all the instructors' names, I could find a way to count the number of "Xs" in column C and D for each Instructor's name listed in Column E so we could track the number of assignments per instructor each month.
I'v been playing with it, but thought I would ask as it is likely your network has a solution.
Take a look at COUNTIFS.
If the instructor name is in column A, the count of AM's for that person would be
=COUNTIFS(Ship Simulator Schedule::E,A,Ship Simulator Schedule::C, "X")
You might like using the checkbox format instead of the X's so you can click on it vs having to type an X. Checkboxes are either TRUE or FALSE. The formula would then be
=COUNTIFS(Ship Simulator Schedule::E,A,Ship Simulator Schedule::C, TRUE)
Worked great! I'm done and could not have completed it without your help.
I had to figure how to send just the first sheet as a PDF as I don't want to send all the other data. I found that in the print options to print just a the sheet as a PDF.
Numbers Sheet 2 Data to Sheet 1
