Lookup formulas
Find a value and bring back what goes with it: by ID, by two keys, the last match, the closest match. 119 answers, each checked by running it, with a live example and an exercise.
How to use VLOOKUP to find an exact match in Excel
=VLOOKUP(G1,A2:D9,3,FALSE)Use =VLOOKUP(lookup_value, table, column_number, FALSE). The FALSE at the end makes it look for an exact match.
How to look up a value to the left in Excel
=XLOOKUP(G1,D2:D7,A2:A7)Use XLOOKUP, which can return any column: =XLOOKUP(value, lookup_column, result_column). In older versions use INDEX and MATCH.
How to look up a value with two criteria in Excel
=XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8)Multiply two tests to build the lookup array: =XLOOKUP(1,(range1=crit1)*(range2=crit2),result_range).
How to find the last occurrence of a value in Excel
=XLOOKUP(F1,B2:B8,A2:A8,,0,-1)Use XLOOKUP with search mode -1 to search from the bottom up: =XLOOKUP(value, lookup_range, return_range,,0,-1).
How to look up a value between two numbers in Excel
=XLOOKUP(E1,A2:A6,B2:B6,,-1)Put the lower bound of each band in a sorted column and use an approximate match: =XLOOKUP(value, lower_bounds, results,,-1).
How to return a message instead of #N/A in a lookup
=XLOOKUP(G1,A2:A9,C2:C9,"Not found")Give XLOOKUP its if_not_found argument, =XLOOKUP(value, lookup, return, "Not found"), or wrap an older lookup in IFNA.
How to use XLOOKUP in Excel
=XLOOKUP(G1,A2:A9,D2:D9)Give XLOOKUP the value to find, the range to search and the range to return:
=XLOOKUP(G1,A2:A9,D2:D9). It looks for an exact match by default.How to use INDEX and MATCH together in Excel
=INDEX(D2:D9,MATCH(G1,B2:B9,0))MATCH finds the row and INDEX returns the value from that row:
=INDEX(D2:D9,MATCH(G1,B2:B9,0)).How to find the position of a value in a list in Excel
=MATCH(E1,A2:A11,0)Use
=MATCH(E1,A2:A11,0). It returns where the value sits in the range (1 for the first cell), and #N/A if it isn't there.How to return the row number of a matching value in Excel
=MATCH(F1,A2:A9,0)+ROW(A2)-1Add the first row's offset to MATCH:
=MATCH(F1,A2:A9,0)+ROW(A2)-1gives the sheet row of the match.How to use HLOOKUP to look up a value in a row in Excel
=HLOOKUP(J1,B1:G4,2,FALSE)Use
=HLOOKUP(J1,B1:G4,2,FALSE): it searches the top row of the table and returns the value from the row number you give, here row 2.How to do a two-way lookup by row and column in Excel
=INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0))Use INDEX with one MATCH for the row and one for the column:
=INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0)).How to make the VLOOKUP column number dynamic in Excel
=VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE)Let MATCH find the column number from the header:
=VLOOKUP(I1,A2:F8,MATCH(I2,A1:F1,0),FALSE).How to use INDEX MATCH MATCH with multiple criteria in Excel
=INDEX(C2:E7,MATCH(1,(A2:A7=H1)*(B2:B7=H2),0),MATCH(H3,C1:E1,0))Find the row with
MATCH(1,(range1=crit1)*(range2=crit2),0)and the column with a normal MATCH, then let INDEX return the crossing cell.How to return all matches of a lookup in Excel
=FILTER(A2:A11,B2:B11=G1,"No orders")VLOOKUP and XLOOKUP stop at the first match. Use FILTER to return every one:
=FILTER(A2:A11,B2:B11=G1,"No orders").How to find the nth match in Excel
=INDEX(FILTER(A2:A10,B2:B10=F1),H1)Filter the matches and pick the one you want:
=INDEX(FILTER(A2:A10,B2:B10=F1),H1)returns match number H1.How to return multiple columns from one lookup in Excel
=XLOOKUP(H1,A2:A9,B2:E9)Give XLOOKUP a return range several columns wide:
=XLOOKUP(H1,A2:A9,B2:E9)spills the whole record across.How to return non-adjacent columns with XLOOKUP in Excel
=XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7))Pick the columns with CHOOSECOLS inside the return range:
=XLOOKUP(J1,A2:A8,CHOOSECOLS(A2:G8,2,6,7)).How to return multiple matches across a row in Excel
=TRANSPOSE(FILTER(B2:B11,A2:A11=E1))Filter the matches and turn the list sideways:
=TRANSPOSE(FILTER(B2:B11,A2:A11=E1))spills them across the row.How to find the last non-blank value in a column in Excel
=XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1)Search from the bottom for the first non-empty cell:
=XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1).How to get the last value in a row in Excel
=XLOOKUP(TRUE,B2:F2<>"",B2:F2,,0,-1)Search the row from the right for a non-empty cell:
=XLOOKUP(TRUE,B2:F2<>"",B2:F2,,0,-1), filled down.How to find the first non-blank value in a row in Excel
=XLOOKUP(TRUE,B2:F2<>"",B2:F2)Look for the first TRUE in a "not empty" test:
=XLOOKUP(TRUE,B2:F2<>"",B2:F2), filled down.How to return the column header of the first non-blank cell in Excel
=XLOOKUP(TRUE,B2:G2<>"",B$1:G$1)Return the header row instead of the values:
=XLOOKUP(TRUE,B2:G2<>"",B$1:G$1).How to find the first value greater than a number in Excel
=XLOOKUP(TRUE,B2:B11>E1,A2:A11)Look for the first TRUE in a comparison:
=XLOOKUP(TRUE,B2:B11>E1,A2:A11)returns the date of the first day above the target.How to do a case-sensitive lookup in Excel
=XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7)Look up TRUE in an EXACT comparison:
=XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7). Normal lookups ignore case.How to look up a partial match with wildcards in Excel
=XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2)Wrap the search text in asterisks and turn on wildcard matching:
=XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2).How to assign a category when a cell contains a keyword from a list
=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$8,A2)),$F$2:$F$8,"Other")Search the cell for every keyword and return the category of the first one found:
=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$8,A2)),$F$2:$F$8,"Other").How to find the closest value to a number in Excel
=XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9)Look up the smallest distance:
=XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9).How to find the next highest value in a list in Excel
=XLOOKUP(E1,B2:B7,A2:A7,"Too big",1)Use XLOOKUP with match mode 1, exact or next larger:
=XLOOKUP(E1,B2:B7,A2:A7,"Too big",1). The list doesn't need to be sorted.How to do an approximate match on data sorted in descending order
=XLOOKUP(E1,A2:A6,B2:B6,,-1)Use XLOOKUP with match mode -1, which doesn't care about sort order:
=XLOOKUP(E1,A2:A6,B2:B6,,-1). VLOOKUP's TRUE needs ascending data.How to find the tax bracket for an income in Excel
=XLOOKUP(E1,A2:A8,B2:B8,,-1)List where each rate starts and use an approximate match:
=XLOOKUP(E1,A2:A8,B2:B8,,-1)returns the rate of the bracket the income falls in.How to calculate income tax with tax brackets in Excel
=LET(band,XLOOKUP(F1,A2:A8,A2:C8,,-1),INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2))Look up the bracket row, then add the tax owed at its start to the income above it times its rate:
=LET(band,XLOOKUP(F1,A2:A8,A2:C8,,-1),INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2)).How to calculate shipping cost by weight in Excel
=XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1)With a rate table of weight limits ("up to"), use XLOOKUP match mode 1, exact or next larger:
=XLOOKUP(E1,A2:A7,B2:B7,"Freight quote",1).How to find which period a date falls in in Excel
=XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1)List the start date of each period and use an approximate match:
=XLOOKUP(E1,B2:B9,A2:A9,"Before sprint 1",-1).How to look up a value between a start and end date in Excel
=XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion")Look for the row where the date is on or after the start and on or before the end:
=XLOOKUP(1,(B2:B7<=G1)*(C2:C7>=G1),A2:A7,"No promotion").How to look up the price in effect on a date in Excel
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9)Find the latest effective date on or before the order date with MAXIFS, then look up the price for that product and date:
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1,B2:B9,"<="&H1)),C2:C9).How to find the name with the highest value in Excel
=XLOOKUP(MAX(B2:B7),B2:B7,A2:A7)Look up the maximum and return the name beside it:
=XLOOKUP(MAX(B2:B7),B2:B7,A2:A7).How to find the name of the second highest value in Excel
=INDEX(SORTBY(A2:A9,B2:B9,-1),E1)Sort the names by value and pick the one at that rank:
=INDEX(SORTBY(A2:A9,B2:B9,-1),E1), with 2 in E1.How to find the top seller in each region in Excel
=XLOOKUP(1,($B$2:$B$10=E2)*($C$2:$C$10=MAXIFS($C$2:$C$10,$B$2:$B$10,E2)),$A$2:$A$10)Find each region's highest sales with MAXIFS and look up the rep with that region and amount:
=XLOOKUP(1,($B$2:$B$10=E2)*($C$2:$C$10=MAXIFS($C$2:$C$10,$B$2:$B$10,E2)),$A$2:$A$10).How to return the column header of the lowest value in a row in Excel
=XLOOKUP(MIN(B2:D2),B2:D2,B$1:D$1)Look up the row's minimum in the row and return the header above it:
=XLOOKUP(MIN(B2:D2),B2:D2,B$1:D$1).How to return the column header where a value is found in Excel
=TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,""))Return the header of every column where the value appears:
=TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,"")).How to list the column headers of cells marked with an X in Excel
=TEXTJOIN(", ",TRUE,IF(B2:F2="X",B$1:F$1,""))Return the header wherever the row has an X and join the results:
=TEXTJOIN(", ",TRUE,IF(B2:F2="X",B$1:F$1,"")), filled down.How to find the row and column labels of the largest value in a table
=TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""))Compare every cell with the maximum and join the row and column labels where it matches:
=TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,"")).How to look up a whole column of values at once in Excel
=IF(A2:A8="","",XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found"))Give XLOOKUP the whole column of lookup values and it returns one result per row:
=IF(A2:A8="","",XLOOKUP(A2:A8,E2:E7,F2:F7,"Not found")). In Google Sheets, use ARRAYFORMULA with VLOOKUP.How to use QUERY as a lookup in Google Sheets
=QUERY(A2:D9,"select B, D where A = '"&G1&"'",0)Put the lookup value into the query's where clause:
=QUERY(A2:D9,"select B, D where A = '"&G1&"'",0)returns the chosen columns for the matching row.How to list values that appear in both lists in Excel
=FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0)Keep the items of the first list that the second list counts at least once:
=FILTER(A2:A9,COUNTIF(C2:C8,A2:A9)>0).How to find values in one list that are missing from another in Excel
=FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid")Keep the items the other list doesn't contain:
=FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid").How to compare two columns and return a value from a third in Excel
=XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match")Look each value up in the other list and return the column you need:
=XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match"), filled down.How to return a value by its position number with CHOOSE in Excel
=CHOOSE(B2,"Very poor","Poor","Neutral","Good","Excellent")CHOOSE returns the item at the position you give it:
=CHOOSE(B2,"Very poor","Poor","Neutral","Good","Excellent").How to look up a value that could be in any of several columns
=XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code")Test each column and look for the row where any test is true:
=XLOOKUP(TRUE,((B2:B7=G1)+(C2:C7=G1)+(D2:D7=G1))>0,A2:A7,"Unknown code").How to use XLOOKUP with OR logic in Excel
=XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None")Add the tests and check for at least one match:
=XLOOKUP(TRUE,(B2:B9=G1)+(C2:C9=I1)>0,A2:A9,"None").How to convert letter grades to GPA points in Excel
=XLOOKUP(C2,$F$2:$F$11,$G$2:$G$11)Keep a grade-to-points table and look each grade up with an exact match:
=XLOOKUP(C2,$F$2:$F$11,$G$2:$G$11).How to make a lookup skip blank results in Excel
=XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone")Add a "not blank" test to the lookup:
=XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone")returns the first match that has a value.How to list all names tied for the highest value in Excel
=FILTER(A2:A8,B2:B8=MAX(B2:B8))Filter for every row equal to the maximum:
=FILTER(A2:A8,B2:B8=MAX(B2:B8)). A normal lookup returns only the first.How to nest one lookup inside another in Excel
=XLOOKUP(XLOOKUP(H1,A2:A8,B2:B8),D2:D5,E2:E5)Use the result of the first lookup as the value of the second:
=XLOOKUP(XLOOKUP(H1,A2:A8,B2:B8),D2:D5,E2:E5).How to keep a lookup blank until a value is entered in Excel
=IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU"))Test the input first:
=IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU"))stays empty on empty rows and still flags typos.How to find the next date after a given date in a list
=MINIFS(B2:B9,B2:B9,">"&E1)Take the smallest date that is later than the given one:
=MINIFS(B2:B9,B2:B9,">"&E1). The list can be in any order.How to get the previous value for the same item in Excel
=XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1)Look up the item in the rows above only, searching from the bottom:
=XLOOKUP(A2,A$1:A1,C$1:C1,"",0,-1), filled down.How to multiply a looked-up price by a quantity in Excel
=B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7)Multiply the quantity by the lookup:
=B2*XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7), filled down.How to sum lookup results for a list of items in Excel
=SUM(XLOOKUP(A2:A6,C2:C10,D2:D10))Look up the whole list at once and add the results:
=SUM(XLOOKUP(A2:A6,C2:C10,D2:D10)).How to sum a matching row across several columns in Excel
=SUM(XLOOKUP(H1,A2:A7,B2:E7))Return the whole row with XLOOKUP and add it up:
=SUM(XLOOKUP(H1,A2:A7,B2:E7)).How to look up a value from a list typed into the formula
=XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown")Use array constants as the lookup and return ranges:
=XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown").How to look up each item in a comma-separated list in Excel
=TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?"))Split the text, look up every piece and join the results:
=TEXTJOIN(", ",TRUE,XLOOKUP(TRIM(TEXTSPLIT(G1,",")),A2:A9,B2:B9,"?")).How to interpolate between values in a lookup table in Excel
=LET(i,MATCH(E1,A2:A8,1),dLo,INDEX(A2:A8,i),dHi,INDEX(A2:A8,i+1),vLo,INDEX(B2:B8,i),vHi,INDEX(B2:B8,i+1),vLo+(E1-dLo)*(vHi-vLo)/(dHi-dLo))Find the row below the value with MATCH, then take the straight line to the next row:
=LET(i,MATCH(E1,A2:A8,1),dLo,INDEX(A2:A8,i),dHi,INDEX(A2:A8,i+1),vLo,INDEX(B2:B8,i),vHi,INDEX(B2:B8,i+1),vLo+(E1-dLo)*(vHi-vLo)/(dHi-dLo)).How to calculate tiered pricing in Excel
=SUMPRODUCT((F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5)*C2:C5)Charge each tier's slice at its own rate and add them up:
=SUMPRODUCT((E1>A2:A5)*(IF(E1<B2:B5,E1,B2:B5)-A2:A5)*C2:C5).How to look up IDs that are formatted differently in Excel
=XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found")Strip the characters that differ from both sides inside the lookup:
=XLOOKUP(SUBSTITUTE(F1,"-",""),SUBSTITUTE(A2:A7,"-",""),C2:C7,"Not found").How to find the last match with multiple criteria in Excel
=XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1)Multiply the tests and search from the bottom:
=XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1).How to do a two-way lookup with an approximate match in Excel
=INDEX(B2:D6,MATCH(G1,A2:A6,1),MATCH(G2,B1:D1,0))Use an approximate MATCH for the banded side and an exact one for the other:
=INDEX(B2:D6,MATCH(G1,A2:A6,1),MATCH(G2,B1:D1,0)).How to fix a lookup when numbers are stored as text
=XLOOKUP(F1&"",A2:A8&"",B2:B8)Compare both sides as text:
=XLOOKUP(F1&"",A2:A8&"",B2:B8). Joining "" turns a number into text, so 10457 matches "10457".How to stop a lookup returning 0 for blank cells
=LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price))Check the result before showing it:
=LET(price,XLOOKUP(E2,$A$2:$A$7,$C$2:$C$7),IF(price="","",price)).Why VLOOKUP returns #N/A when copied down
=VLOOKUP(A2,$E$2:$F$8,2,FALSE)The table range moves as you copy the formula. Lock it with
$:=VLOOKUP(A2,$E$2:$F$8,2,FALSE).Why a lookup returns a number instead of a date
=XLOOKUP(F1,A2:A8,C2:C8)The lookup is right; the result cell just isn't formatted as a date. Excel stores dates as numbers, so give the cell a date format, or wrap the result in TEXT for a label.
How to look up a date when dates are stored as text
=XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8)Convert the text to a real date inside the lookup:
=XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8).How to look up a value that contains an asterisk or question mark
=XLOOKUP(F1,A2:A6,C2:C6)Use XLOOKUP, which treats * and ? as ordinary characters by default:
=XLOOKUP(F1,A2:A6,C2:C6). VLOOKUP and MATCH read them as wildcards.How to use INDEX MATCH with multiple criteria in Google Sheets
=ARRAYFORMULA(INDEX(D2:D9,MATCH(1,(A2:A9=G1)*(B2:B9=I1),0)))Wrap the formula in ARRAYFORMULA so the tests run on every row:
=ARRAYFORMULA(INDEX(D2:D9,MATCH(1,(A2:A9=G1)*(B2:B9=I1),0))).How to look up a value with a regular expression in Google Sheets
=FILTER(A2:B9,REGEXMATCH(A2:A9,E1))Use REGEXMATCH as the condition in FILTER:
=FILTER(A2:B9,REGEXMATCH(A2:A9,E1))returns every row whose text matches the pattern.How to get the latest status for an ID from an unsorted log
=XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10)Find the ID's latest date with MAXIFS and look up the row with that ID and date:
=XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10).How to look up a value for the month of a date in Excel
=XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7)Turn the date into the first day of its month and look that up:
=XLOOKUP(EOMONTH(E1,-1)+1,A2:A7,B2:B7).How to look up a rate for a date or the closest date before it
=XLOOKUP(E1,A2:A9,B2:B9,,-1)Use an approximate match on the dates:
=XLOOKUP(E1,A2:A9,B2:B9,,-1)returns the rate on that date or, if there is none, the last one before it.How to find which shift a time falls in in Excel
=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)List each shift's start time, starting at midnight, and use an approximate match:
=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1).How to get the last 3 entries for a customer in Excel
=TAKE(FILTER(A2:C12,B2:B12=F1),-3)Filter the customer's rows and take the last three:
=TAKE(FILTER(A2:C12,B2:B12=F1),-3).How to return the cell below a matching value in Excel
=INDEX(A2:A11,MATCH(D1,A2:A11,0)+1)Find the label with MATCH and add one row:
=INDEX(A2:A11,MATCH(D1,A2:A11,0)+1).How to return the category from a merged cell above in Excel
=IF(A2="",C1,A2)Repeat the last category until a new one appears:
=IF(A2="",C1,A2), filled down.How to find the position of the largest value in Excel
=XMATCH(MAX(B2:B13),B2:B13)Match the maximum within the range:
=XMATCH(MAX(B2:B13),B2:B13)returns where it is, here month 11.How to use INDEX to return a value by row and column number
=INDEX(B2:F6,I1,K1)Give INDEX the range, then the row and column positions inside it:
=INDEX(B2:F6,I1,K1).How to check if a value exists in a list in Excel
=ISNUMBER(MATCH(D1,A2:A9,0))Use
=ISNUMBER(MATCH(D1,A2:A9,0)): MATCH finds a position if the value is there, and ISNUMBER turns that into TRUE or FALSE.How to switch which column a formula uses with CHOOSE in Excel
=SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7))Let CHOOSE pick the range:
=SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7))totals the Low, Base or High column depending on the number in G1.How to return the next item in a list after a given value
=INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1)Find the value's position, add one and wrap around at the end:
=INDEX(A2:A7,MOD(MATCH(D1,A2:A7,0),ROWS(A2:A7))+1).How to merge two tables by a common ID in Excel
=HSTACK(A2:B7,XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing"))Keep the first table and add the matching column from the second with one lookup:
=HSTACK(A2:B7,XLOOKUP(A2:A7,D2:D6,E2:E6,"Missing")).How to look up a date when the data has times in Excel
=XLOOKUP(G1,INT(A2:A9),B2:B9)Strip the times inside the lookup:
=XLOOKUP(G1,INT(A2:A9),B2:B9). INT drops the time part of each timestamp.How to assign a group by the first letter of a name in Excel
=XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1)List the first letter of each group and use an approximate match:
=XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1).How to look up a value using the first few characters of a code
=XLOOKUP(LEFT(A2,3),$E$2:$E$7,$F$2:$F$7,"Unknown")Cut the code down with LEFT and look that up:
=XLOOKUP(LEFT(A2,3),$E$2:$E$7,$F$2:$F$7,"Unknown").How to look up a rate based on the day of the week in Excel
=INDEX($F$2:$F$8,WEEKDAY(A2,2))Use the weekday number as the position in a Monday-to-Sunday rate table:
=INDEX($F$2:$F$8,WEEKDAY(A2,2)).How to get the latest value in a row for a matching name
=LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,0,-1))Look up the person's row, then take its last filled cell:
=LET(vals,XLOOKUP(J1,A2:A6,B2:G6),XLOOKUP(TRUE,vals<>"",vals,,0,-1)).How to return the supplier with the lowest price for an item
=XLOOKUP(1,(A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1)),B2:B10)Find the item's lowest price with MINIFS and look up the row with that item and price:
=XLOOKUP(1,(A2:A10=G1)*(C2:C10=MINIFS(C2:C10,A2:A10,G1)),B2:B10).How to get the value on the most recent date in Excel
=XLOOKUP(MAX(A2:A7),A2:A7,B2:B7)Look up the latest date and return the value beside it:
=XLOOKUP(MAX(A2:A7),A2:A7,B2:B7). The list can be in any order.How to get the most recent entry for every customer in Excel
=SORT(FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11)))Keep the rows whose date is that customer's latest:
=SORT(FILTER(A2:C11,B2:B11=MAXIFS(B2:B11,A2:A11,A2:A11))).How to sum a column by its header name in Excel
=SUM(XLOOKUP(I1,B1:F1,B2:F7))Return the column with a horizontal XLOOKUP and add it up:
=SUM(XLOOKUP(I1,B1:F1,B2:F7)).How to find the first blank cell in a column in Excel
=XLOOKUP(TRUE,B2:B9="",A2:A9,"Full")Look for the first TRUE in a blank test:
=XLOOKUP(TRUE,B2:B9="",A2:A9,"Full")returns the slot next to the first empty cell.How to find the last row with data in a column in Excel
=XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1)Search from the bottom for a filled cell and return its row number:
=XLOOKUP(TRUE,A2:A15<>"",ROW(A2:A15),,0,-1).How to look up codes that have leading zeros in Excel
=XLOOKUP(TEXT(F1,"00000"),A2:A8,B2:B8,"Not found")Pad the typed number back to the code's length with TEXT:
=XLOOKUP(TEXT(F1,"00000"),A2:A8,B2:B8,"Not found").How to find the last date a value was above a target in Excel
=XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1)Search from the bottom for the first value above the target:
=XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1).How to look up a value from a table with minimum and maximum columns
=XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible")Find the row whose minimum is at or below the value and whose maximum is at or above it:
=XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible").How to look up a value in two tables in Excel
=XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found"))Search the second table when the first has no match, using XLOOKUP's if_not_found argument:
=XLOOKUP(H1,A2:A6,B2:B6,XLOOKUP(H1,D2:D5,E2:E5,"Not found")).How to speed up XLOOKUP on large sorted data
=XLOOKUP(F1,A2:A13,B2:B13,"Not found",0,2)Tell XLOOKUP the data is sorted with search mode 2:
=XLOOKUP(F1,A2:A13,B2:B13,"Not found",0,2). It then halves the list at each step instead of reading every row.How to look up the weekly column for a date in Excel
=XLOOKUP(J2,A2:A5,XLOOKUP(J1,B1:G1,B2:G5,,-1))Match the date to the week starting on or before it, then pick the person's row:
=XLOOKUP(J2,A2:A5,XLOOKUP(J1,B1:G1,B2:G5,,-1)).How to convert currencies with an exchange rate table in Excel
=ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2)Multiply each amount by the rate looked up for its currency:
=ROUND(B2*XLOOKUP(C2,$F$2:$F$6,$G$2:$G$6),2).How to compare two price lists and find changed prices in Excel
=LET(old,XLOOKUP(D2:D8,A2:A7,B2:B7,"New"),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8))Look up each item's old price and keep the rows where it differs:
=LET(old,XLOOKUP(D2:D8,A2:A7,B2:B7,"New"),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8)).How to find the first value within a tolerance of a target in Excel
=XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match")Test the distance from the target against the tolerance:
=XLOOKUP(TRUE,ABS(B2:B8-E1)<=G1,A2:A8,"No match").How to find the cheapest option that meets a minimum requirement in Excel
=XLOOKUP(1,(B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1)),A2:A8,"None")Find the lowest price among the options that qualify, then look up that option:
=XLOOKUP(1,(B2:B8>=F1)*(C2:C8=MINIFS(C2:C8,B2:B8,">="&F1)),A2:A8,"None").How to find who is on duty for a date in a rotation in Excel
=INDEX(A2:A5,MOD(INT((G1-E1)/7),ROWS(A2:A5))+1)Count whole weeks since the rotation started and wrap around the list:
=INDEX(A2:A5,MOD(INT((G1-E1)/7),ROWS(A2:A5))+1).How to return the first item from a priority list that appears in another list
=XLOOKUP(TRUE,COUNTIF(C2:C5,A2:A6)>0,A2:A6,"None available")Check each priority item against the other list and return the first that is there:
=XLOOKUP(TRUE,COUNTIF(C2:C5,A2:A6)>0,A2:A6,"None available").How to find the nearest location by coordinates in Excel
=LET(dist,SQRT((B2:B7-G1)^2+(C2:C7-I1)^2),XLOOKUP(MIN(dist),dist,A2:A7))Work out every distance and look up the smallest:
=LET(dist,SQRT((B2:B7-G1)^2+(C2:C7-I1)^2),XLOOKUP(MIN(dist),dist,A2:A7)).How to find the column number of a header in Excel
=XMATCH(K1,A1:H1)Match the header name in the header row:
=XMATCH(K1,A1:H1)returns its column number within the row, here 4.How to get the second to last value in a column in Excel
=CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2)Drop the blanks and count back from the end:
=CHOOSEROWS(FILTER(B2:B11,B2:B11<>""),-2).How to look up a manager's name from the same employee table in Excel
=XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,"")Look up each manager ID in the table's own ID column:
=XLOOKUP(C2,$A$2:$A$9,$B$2:$B$9,""), filled down.How to do an approximate match across a row with HLOOKUP
=HLOOKUP(I1,B1:F2,2,TRUE)Use TRUE as HLOOKUP's last argument with the thresholds sorted left to right:
=HLOOKUP(I1,B1:F2,2,TRUE).How to find invoices that are in one list but not the other in Excel
=IF(COUNTIF($E$2:$E$6,A2)=0,"Not in bank","Matched")Count each invoice in the other list: =IF(COUNTIF($E$2:$E$7,A2)=0,"Not in bank","Matched"). A count of 0 means the item is missing.
How to use XLOOKUP with multiple criteria in Excel
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=F2),C2:C9)Multiply the conditions into one array and look up 1: =XLOOKUP(1,(A2:A9=G2)*(B2:B9=G3),C2:C9). A row matches only when every condition is true.