=xlookup() and =vlookup()

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]): [range_lookup]

[range_lookup]: OPTIONAL arg that lets you specify whether you want an exact match or an approximate match *if omitted, it defaults to TRUE or 1 = approximate match, so manually type FALSE or 0 if you require an exact match. just put 0 at end of arg, don't need brackets

=XLOOKUP() purpose

allows you to search for a specific entry in a column or row and return corresponding entries in other rows or columns (rows/columns used in search must be the same length)

Drop-down list (if you have a lot of data and do not know them all off the top of your head, you can create a drop-down list of column values that will allow you to choose how you're filtering your results rather than manually entering a condition)

click: data --> data tools --> data validation in data validation dialogue box, click settings and select List from the Allow drop-down. in the source, type = and enter your column range (what you want the drop-down to include), or you can manually highlight the range with your mouse hit enter/click ok!

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]): col_index_num

col_index_num: *NEEDS TO BE A NUMBER (columb B= 2, column D= 4, etc.) like return_array in =XLOOKUP(), col_index_num represents the corresponding column you are interested in (if looking for your test score based on your name on the board, your col_index_number would be the column/range of cells denoting test scores)

create table from data?

highlight data range and hit ctrlT

USING EMPLOYEE NAME/ID EXAMPLE: =XLOOKUP(1111, A:A, E:E)

looking for ID # 1111, located in column A, and the corresponding name found in column E

=VLOOKUP() purpose

looks for a specified value in a column and, once found, scans the row that value is found in for your desired corresponding value from a different column

=XLOOKUP(lookup_value, lookup_array, return_array): lookup_value arg

lookup: the entry/value you are searching for or basing your search on (so, if you want to find the employee name that matches ID # 111, you would put 111 as the lookup value)

=XLOOKUP(lookup_value, lookup_array, return_array): lookup_array arg

lookup_array: the column/row where you think your desired entry/value is located in (so, if you want to find the employee name that matches ID #111 and IDs are listed in column A, you would put, for example, A2:A10)

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]): lookup_value arg

lookup_value: value you are basing your search on (if looking for your test score based on your name on the board, your lookup_value would be your name)

=XLOOKUP(lookup_value, lookup_array, return_array): return_array arg

return_array: the column/row corresponding to your lookup_array that you want your result to come from (so, if you want to find the employee name that matches ID #111 and names are listed in column D, you would put, for example, D2:D10)

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]): table_array

table_array: highlight at least all your columns of interest (your staring_array and your return_array must be in range), and the first column will be where excel looks for your lookup_value EXAMPLE: =VLOOKUP(H9, B2:D11, 4, 0) tells excel to look for the value entered in H9 within column B (B2:B11) and then go to that row's cell in the 4th column (would be D); 0 indicates that you want an exact match, not approximate (which is default). Ideally, this would return something from column D!

=TRIM($A$2:$A$20)

will make sure all the entries in row A (assuming there are 18 entries from A2 to A20) do not have an extra leading or trailing space that would effect your =VLOOKUP() or XLOOKUP() results

=VLOOKUP() limitations

1.) cannot find values to the left so, if your starting column (column of reference) is to the right of desired data, it will not work (ex: if names column is the last column all the way on the right and test scores are to the left, you could not use =VLOOKUP() to find a test score based on someone's name 2.) only works with data arranged vertically

=VLOOKUP(F9, $A$2:$E$6, MATCH(F11, $A$1:$E$1, 0), 0) interp and MATCH() ?

MATCH() allows you to make the column index dynamic as well, which is useful (for example) if you have a list of students and their grades in different subjects; in using the MATCH() function as your col_index_num, you allow yourself to dynamically change both the name and subject grade you're looking for GIVEN EXAMPLE: =VLOOKUP(F9, $A$2:$E$6, MATCH(F11, $A$1:$E$1, 0), 0) this function tells you that, based on the name you enter in F9, find that name in the table entered from columns A to E and rows 2 to 6 and after, based on the subject you enter in F11, find that subject within A1 to E1 and return the exact grade if F9 = Lexie and F11 = Math, this formula would tell you Lexie's math score. To figure out Lily's Chem score, all you'd have to do is type Lily (no quotation marks) into F9 and Chem into F11--this is what makes the formula dynamic in 2 ways!

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23) The first argument of the formula is IF(H2="Unit Test",1,IF(H2="Midterm",2,3)), which checks the cell H2 and see what level of exam is being referred to. If it's Unit Test, it returns $A$3:$E$7, which has the scores for Unit Test. If it's Midterm, it returns $A$11:$E$15, else it returns $A$19:$E$23. Doing this makes the VLOOKUP table array dynamic and hence makes it a three-way lookup.