by The tutorial explains the vector and array forms of the Excel LOOKUP function and demonstrates typical and non-trivial uses of LOOKUP in Excel with formula examples.
One of the most frequent questions that every Excel user asks once in a while is this: "How do I look up a value on one sheet and pull a matching value to another sheet?". Of course, there can be many variations of the basic scenario: you may be looking for the closest match rather than exact match, you may want to search vertically in a column or horizontally in a row, evaluate one or multiple criteria, etc. However, the essence is the same - you need to know how to look up in Excel.
Microsoft Excel provides a handful of different ways to do lookup. To begin with, let's learn a function that is designed to handle the simplest cases of vertical and horizontal lookup. As you can easily guess, I am talking about the LOOKUP function.
Excel LOOKUP function - syntax and uses
At the most basic level, the LOOKUP function in Excel searches a value in one column or row and returns a matching value from the same position in another column or row.
There are two forms of LOOKUP in Excel: Vector and Array. Each form is explained individually below.
Excel LOOKUP function - vector form
In this context, a vector refers to a one-column or one-row range. Consequently, you use the vector form of LOOKUP to search one row or one column of data for a specified value, and pull a value from the same position in another row or column.
The syntax of the vector Lookup is as follows:
Where:
- Lookup_value (required) - a value to search for. It can be a number, text, logical value of TRUE or FALSE, or a reference to a cell containing the lookup value.
- Lookup_vector (required) - one-row or one-column range to be searched. It must be sorted in ascending order.
- Result_vector (optional) - one-row or one-column range from which you want to return the result - a value in the same position as the lookup value. Result_vector must be the same size as lookup_range. If omitted, the result is returned from lookup_vector.
The following examples demonstrate two simple Lookup formulas in action.
Vertical Lookup formula - search in one-column range
Let's say, you have a list of sellers in column D (D2:D5) and the products they sold in column E (E2:E5). You are creating a dashboard where your users will enter the seller's name in B2 and you need a formula that would pull a corresponding product in B3. The task can be easily accomplished with this formula:
=LOOKUP(B2,D2:D5,E2:E5)
To better understand the arguments, please have a look at this screenshot:
Horizontal Lookup formula - search in one-row range
If your source data has a horizontal layout, i.e. the entries reside in rows rather than columns, then supply a one-row range in the lookup_vector and result_vector arguments, like this:
=LOOKUP(B2,E1:H1,E2:H2)
In the second part of this tutorial, you will find a few more Excel Lookup examples that solve more complex tasks. In the meantime, please remember the following simple facts that will help you bypass possible pitfalls and prevent common errors.
5 things you should know about vector form of Excel LOOKUP
- Values in lookup_vector should be sorted in ascending order, i.e. from smallest to largest or from A to Z, otherwise your Excel Lookup formula may return an error or incorrect result. If you need to do lookup on unsorted data, then use either INDEX MATCH or OFFSET MATCH.
- Lookup_vector and result_vector must be a one-row or one-column range of the same size.
- The LOOKUP function in Excel is case-insensitive, it does not differentiate uppercase and lowercase text.
- Excel LOOKUP works based on approximate match. More precisely, a Lookup formula searches for exact match first. If it cannot find the lookup value exactly, it looks up the next smallest value, i.e. the largest value in lookup_vector that is less than or equal to lookup_value.
For example, if your lookup value is "5", the formula will search it first. If "5" is not found, it will search "4". If "4" is not found, it will search "3", and so on.
- If lookup_value is smaller than the smallest value in lookup_vector, Excel LOOKUP returns the #N/A error.
Excel LOOKUP function - array form
The array form of the LOOKUP function searches the specified value in the first column or row of the array and retrieves a value from the same position in the last column or row of the array.
The array Lookup has 2 arguments, both of which are required:
Where:
- Lookup_value - a value to search for in an array.
- Array - a range of cells where you want to search for the lookup value. The values in the first column or row of the array (depending on whether you do V-lookup or H-lookup) must be sorted in ascending order. Uppercase and lowercase characters are deemed equivalent.
For example, with the seller names located in the first column of the array (column A) and order dates in the last column of the array (column C), you can use the following formula to search the name and pull the matching date:
=LOOKUP(B2,D2:F5)
Note. The array form of the Excel LOOKUP function should not be confused with Excel array formulas. Although it operates on arrays, LOOKUP is still a regular formula, which is completed in the usual way by pressing the Enter key.
4 things you should know about array form of Excel LOOKUP
- If array has more rows than columns or the same number of columns and rows, a Lookup formula searches in the first column (horizontal lookup).
- If array has more columns than rows, Excel LOOKUP searches in the first row (vertical lookup).
- If a formula can't find the lookup value, it uses the largest value in the array that is less than or equal to lookup_value.
- If the lookup value is smaller than the smallest value in the first column or row of the array (depending on the array dimensions), a Lookup formula returns the #N/A error.
Important note! The functionality of the Excel LOOKUP array form is limited and we don't recommend using it. Instead, you can use the VLOOKUP or HLOOKUP function, which are the improved versions to do vertical and horizontal lookup, respectively.
How to use LOOKUP function in Excel - formula examples
Although there exist more powerful functions to look up and match in Excel (which is the subject of our next tutorial), LOOKUP comes in handy in many situations, and the following examples demonstrate a couple of non-trivial uses. Please note, all of the below formulas use the vector form of Excel LOOKUP.
Look up a value in the last non-blank cell in a column
If you have a column of dynamically populated data, you may want to pick the most recently added entry, i.e. get the last non-empty cell in a column. For this, use this generic formula:
In the above formula, all arguments except for the column reference are constants. So, to retrieve the last value in a specific column, you just need to supply the corresponding column reference. For example, to extract the value of the last non-blank cell in column A, use this formula:
=LOOKUP(2, 1/(A:A<>""), A:A)
To get the last value from other columns, modify the column references like shown in the screenshot below - first reference is the column to be checked for blank/non-blank cells, and the second reference is the column to return the value from:
How this formula works
In the lookup_value argument, you supply 2 or any other number greater than 1 (in a moment, you will understand why).
In the lookup_vector argument, you put this expression: 1/(A:A<>"")
- First, you perform the logical operation A:A<>"" that compares each cell in column A with an empty string and returns TRUE for non-empty cells and FALSE for empty cells. In the above example, the formula in F2 returns this array: {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;...}
- Then, you divide the number 1 by each element of the above array. With TRUE equating to 1 and FALSE equating to 0, you get a new array consisting of 1's and #DIV/0! errors (the result of dividing by 0), and this array is used as lookup_vector. In this example, it's {1;1;1;1;1;#DIV/0!;...}
Now, how it comes that the formula returns the last non-empty value in a column, given that lookup_value does not match any element of lookup_vector? The key to understanding the logic is that Excel LOOKUP searches with approximate match, i.e. when the exact lookup value is not found, it matches the next biggest value in lookup_vector that is smaller than lookup_value. In our case, lookup_value is 2 and the largest value in lookup_vector is 1, so LOOKUP matches the last 1 in the array, which is the last non-empty cell.
In the result_vector argument, you reference the column from which you want to return a value, and your Lookup formula will fetch the value in the same position as the lookup value.
Tip. If you wish to get the number of the row holding the last value, then use the ROW function to retrieve it. For example: =LOOKUP(2,1/(A:A<>""),ROW(A:A))
Look up a value in the last non-blank cell in a row
If your source data is laid out in rows rather than columns, you can get the value of the last non-empty cell using this formula:
In fact, this formula is nothing else but a slight modification of the previous formula, with the only difference that you use the row reference instead of the column reference.
For example, to get the value of the last non-empty cell in row 1, use this formula:
=LOOKUP(2, 1/(1:1<>""), 1:1)
The following screenshot shows the result:
Get a value associated with the last entry in a row
With just a little creativity, the above formula can easily be customized for solving other similar tasks. For instance, it can be used to get a value associated with the last instance of a specific value in a row. This may sound a bit obscure, but the following example will make things easier to understand.
Assuming you have a summary table where column A contains the seller names and subsequent columns contain data of some kind for each month. In this example, a cell contain "yes" if a given seller has closed at least one deal in a given month. Our goal is to get a month associated with the last "yes" entry in a row.
The task can be solved by using the following LOOKUP formula:
=LOOKUP(2, 1/(B2:H2="yes"), $B$1:$H$1)
The formula's logic is basically the same as described in the first example. The difference is that you use the "equal to" operator ("=") instead of "not equal to" ("<>") and operate on rows instead of columns.
The following screenshot demonstrates a result:
Lookup as alternative to nested IFs
In all of the Lookup formulas we've discussed so far, the lookup_vector and result_vector arguments were represented by range references. However, the syntax of the Excel LOOKUP function allows supplying the vectors in the form of a vertical array constant, which enables you to replicate the functionality of nested IF with a more compact and easy-to-read formula.
Let's say, you have a list of abbreviations in column A and you want to replace them with full names, where "C" stands for "Completed", "D" is "Development, and "T" is "Testing". The task can be accomplished with the following nested IF function:
=IF(A2="c", "Completed", IF(A2="d", "Development", IF(A2="t", "Testing", "")))
Or, by using this Lookup formula:
=LOOKUP(A2, {"c";"d";"t"}, {"Completed";"Development";"Testing"})
As shown in the screenshot below, both formulas yield identical results:
Note. For an Excel Lookup formula to work correctly, the values in lookup_array should be sorted from A to Z or from smallest to largest.
If you are pulling values from a lookup table, then you can embed a Vlookup function in the lookup_value argument to retrieve a match.
Assuming the lookup value is in cell E2, the lookup table is A2:C7, and the column of interest ("Status") is the 3rd column in the lookup table, the following formula does the job:
=LOOKUP(VLOOKUP(E2, $A$2:$C$7, 3, FALSE), {"c";"d";"t"}, {"Completed";"Development";"Testing"})
As demonstrated in the screenshot below, the formula retrieves the project status from the lookup table and replaces an abbreviation with the corresponding word:
Tip. In Excel 365, you can use the SWITCH function for similar purposes.
I hope these examples have shed some light on how the LOOKUP function works. In the next tutorial, we will discuss a few other ways to do lookup in Excel and explain which Lookup formula is best to be used in which situation. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel Lookup examples (.xlsm file)
90 comments
Hi there,
I have a task to search for top seller in a table where my data is not sorted.
Based on a single month, how can I search how many times a seller appears and also take the totals they have sold and sort it out.
Hello Lloyd!
If I understand your task correctly, this guide may be helpful: Get data associated with the n-th largest value.
If table has more than one record of sales for each seller, then SUMIFS formula is used to find the total sales for the seller for the month.
In this case, you can get a list of all sellers using the UNIQUE formula.
You can also get all the sales totals you need using the pivot table and these instructions: How to make and use Pivot Table in Excel.
Can't give a more precise answer since you haven't given any information about your data.
Hello, I'd like to specify "Not Found" when the lookup value is not in the lookup vector? How do I do it using Lookup function in Excel? Thanks.
Hi! To get text instead of an error message, use the IFERROR function. For example:
=IFERROR(LOOKUP(B2,D2:D5,E2:E5),"Not found")
I am trying to use the LOOKUP function, I have codes for some persons, these codes are combination of numerical and alphabetical characters. the LOOKUP function is working very well except when the code is starting with 0 or 1. Are there any explanation for that, and what can I do.
Thank you
I’m sorry, but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
Thank you for your response, I prepared an illustration to clarify my point
In this example, I have a unique code for each person, some codes have been selected by other department, and I have to send these persons emails. So I used the lookup function to check who of my list has to receive an email. There is an example of the lookup function that I used.
I checked by the code in cell A2 (Lookup_value), in the vector cell A16 to cell A19 (lookup_vector).
As I understand if the contents of A2 is included in the lookup vector, this value will be return.
Using the shown formula, all answers were correct (either True or False) except the one with code "07E636D0"
Code Name Send Email?
56E4FE26 John FALSE "=LOOKUP($A2,$A$16:$A$19)=$A2"
ED58035C Mark FALSE
E58C9E88 Sarah TRUE
16DC22EA George TRUE
3BBDD787 Usama TRUE
6725593E Jasmin FALSE
07E636D0 Michel #N/A
35F11E43 Hasmid FALSE
A23A7058 Ernieza FALSE
90BFA9D0 Akmal FALSE
Code of Selected Persons
16DC22EA
3BBDD787
E58C9E88
07E636D0
Hi! The values in the lookup_vector (A16 to A19) must be sorted in ascending order, i.e. from smallest to largest or A to Z, or otherwise your lookup formula may return an error or incorrect result. Read the article above carefully.
Thank you, I tried to sort the lookup_vector, as in the following example, but it gave me same error
Code Name Send Email?
07E636D0 Aref #N/A "=LOOKUP($A2,$A$20:$A$31)=$A2"
13C6D63C Luay #N/A
16DC22EA Saher #N/A
1D9BAD30 Zac #N/A
24B05219 Mahm #N/A
24B0EB0F Irene #N/A
250C4E31 Ihab #N/A
2749E54B Moham #N/A
2A4B7669 Hnay #N/A
35F11E43 Sarah #N/A
3BBDD787 Cani #N/A
46DBF3C2 Adam TRUE
48441A3A Ibraham FALSE
56E4FE26 Hana FALSE
5D723A44 William C FALSE
Code of Selected Persons
46DBF3C2
4CD83BD4
533ADA8D
62CB0960
62CB0960
90BFA9D0
94F6970A
9EA377EA
AFA0A326
EE82CFBC
F4CE3F54
F4CE3F54
It's hard to find values that aren't there. Take a closer look at your data.
I have unique student IDs in column, and in other columns I have their subjects. I want to generate exam fee slip for them. I have subject and its fee in other columns. for receipt/slip generation, the formula against the subject should read if there is fee mentioned in the desired column under the subject. It should automatically populate it in row on the slip page alongwith its respective fee in the next column. I would prefer match index formula since my columns in data page will may change. My data is in such format
C:C D:D
$ 1000 $1100
Accouting Economics
School Id #
columns
B;B
16500
16501
16502
the starting point I think is; if the desired cell is filled( non-empty), the next step
Hi! I can't offer you a formula because I can't figure out how your data is organized. I think this guide will help to solve the problem: Excel INDEX MATCH with multiple criteria - formula examples.
=LOOKUP(AE4, {"MBB";"PBB";"CIMB"}, {"CB";"PB";"MB"})
Appreciate could point out where I went wrong with the above formula. It worked with the following
MBB - MB
PBB - PB
but when input with CIMB it returns #N/A instead of CB
Thanks
Hi!
Please note that Lookup_vector must be sorted in ascending order. You didn't do it.
I am trying to create a sheet for classes out company offers. I have the class name up to about 25 classes. When I put the class name in I want to check 2 other columns for an Value (X) to see if they are a member or non member. and put the price of the class in the next column. I have an array with all the class names and prices for both but i cant figure out how to do it.
Hi! I'm sorry, I'm afraid these pieces of info are not enough to give you a formula. To find the value in the other two columns, try using this guide: How to do sequential VLOOKUPs in Excel.
If this does not help, explain the problem in detail.
I am looking to pull data from one tab into a consolidated table, The tab I am pulling from matches a customer column and date column and needs to pull from a column that may have multiple cells to give a total. What would be the best method to accomplish this in excel.
Hi!
Unfortunately, without seeing your data it is difficult to give you any advice. If you have a specific question about the operation of a function or formula, I will try to answer it.
Hi Svetlana,
Good morning!
I have a duty roster of nurses for the month of Feb. Below here is a sample. I want a formula that can give me the last two days' duty. (E.g EN). Each month we have days ending. I have tried =CONCATENATE but it's not working every month.
E E N N O O M M E E N N O O M M E E N N N O O M M E E N
Hi!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get?
Sir, where can i attache a spread sheet?
Sr. No Code Name City
1 123 abcd xyz
456 efgh abc
2 789 fgju obc
987 tyqw cnn
above data is in sheet one
in another sheet i wanna select only sr no. , for that i m using vlookup but here main problem is the Sr No is using for both upper row and in other sheet i need both row data by selecting sr. no.
for example if i select sr. no. 2 than it will have to show me data of sr. no. 2 which is as below
Code Name City
789 fgju obc
987 tyqw cnn
pls provide me an email address to mail you my problem and data spreadsheet
Hi!
You can find the examples and detailed instructions here: Vlookup multiple matches and return results in a column
I hope it’ll be helpful.
Sr. Code Name Department Branch DOJ CITY
1 125 ABCD A AS 01-02-2020 KOLKATA
521 BCDE B AC 01-04-2021 MUMBAI
688 CDEF E AN 01-09-2020 DELHI
420 EFGH L AM 15-08-2020 AHMEDABAD
297 FGHI P AO 01-07-2020 AGRA
108 GHIJ W AP 01-04-2020 GOA
2 735 XYZ M AC 15-08-2020
926 YZA I AN 01-07-2020
509 ZAB K AM 01-04-2020
645 ABC D AS 15-08-2020
135 BCD Q AC 01-07-2020
900 CDE E AL 01-04-2020
I WANNA MANGE THIS DATA TO ANOTHER SHEET BY SCROLLING A MACRO BUTTON SELECTING ONLY 1ST COLUMN SR NO AND FILL ALL DATA WITHIN CODE 125 TO CITY GOA
CAN ANY ONE HELP ME WITH VLOOKUP OR HLOOKUP OR XLOOKUP FORMULAS
I CAN SHARE EXCEL SHEET TO EXPLAIN MORE CLEARLY
Thanks
Date Activities Planned Actual Shift
2021/06/01 SD 5 2 Day - Shift
2021/06/01 FD 4 2 Day - Shift
2021/06/02 FD 4 2 Day - Shift
2021/06/03 FD 4 2 Day - Shift
2021/06/04 FD 4 2 Day - Shift
2021/06/07 SD 5 2 Day - Shift
2021/06/08 SD 5 2 Day - Shift
sheet 2
Hello!
I can’t give you a formula because I don’t have your data. You can find the examples and detailed instructions here: How to Vlookup multiple criteria in Excel.
Hope you’ll find this information helpful.
I need to look at the current date on sheet 1, then look at sheet 2 same date, shift and activity, then return the actual
I am trying to pull data from sheet 2 to sheet 1 which is a report.
sheet 2
A: date
B: Activities are (SD, FD and DD)
C: Target
D: Actual
E: Shifts: (Day, Afternoon and Night)
so I want to pull the actuals to the report sheet 1
All the 3 activities happens in all 3 shifts
is there anything wrong with this formula? is just not giving me what is suppose to give me
=VLOOKUP((('North Section'!J:J=(TODAY()-1))*('North Section'!K:K="SD")*('North Section'!N:N="Day - Shift "));Flat_file__2;13;FALSE)
I need a Lookup formula to do this:
Lookup if B2 = "CN" and C27 = "A" then get a predefined value.
So if B2 = "CN" and C27 = "A" then show £1
OR if B2 = "CN" and C27 = "B" then show £2
OR if B2 = "CN" and C27 = "C" then show £3
OR if B2 = "CN" and C27 = "D" then show £4
So if B2 = "PI" and C27 = "A" then show £1.50
OR if B2 = "PI" and C27 = "B" then show £2.50
OR if B2 = "PI" and C27 = "C" then show £3.50
OR if B2 = "PI" and C27 = "D" then show £4.50
Can anyone help?
Hello!
If I got you right, the formula below will help you with your task:
=IF(B2="CN",1,0)*MATCH(C27,{"A","B","C","D"},0) + IF(B2="PI",1,0)*(MATCH(C27,{"A","B","C","D"},0)+0.5)
Hope this is what you need.
Your formula does work (thank you) but i need differing cell referrals for a then b then c etc
Currently this kind of works, but it there a better way of writing it?
=IF(AND(B3="cn",(C27="A")),D46,C46)*OR(AND(B3="cn",(C27="B")),D47,C47)*OR(AND(B3="cn",(C27="C")),D48,C48)*OR(AND(B3="cn",(C27="D")),D49,C49)*OR(AND(B3="pi",(C27="A")),D46,C46)*OR(AND(B3="pi",(C27="B")),D47,C47)*OR(AND(B3="pi",(C27="C")),D48,C48)*OR(AND(B3="pi",(C27="D")),D49,C49)
D46-49, C46-49 have different values.
So if B2 = "CN" and C27 = "A" then show D46
OR if B2 = "CN" and C27 = "B" then show D47
OR if B2 = "CN" and C27 = "C" then show D48
OR if B2 = "CN" and C27 = "D" then show D49
So if B2 = "PI" and C27 = "A" then show C46
OR if B2 = "PI" and C27 = "B" then show C47
OR if B2 = "PI" and C27 = "C" then show C48
OR if B2 = "PI" and C27 = "D" then show C49
Thank you
Hello!
I am sorry that I had to answer a question, the answer to which is not useful to you.
The formula below will do the trick for you:
=INDIRECT(IF(B2="CN","D4"&(MATCH(C27,{"A","B","C","D"},0)+5),"") & IF(B2="PI","C4"&(MATCH(C27,{"A","B","C","D"},0)+5),""))
I hope it’ll be helpful.
Brilliant it works. Delighted thank you so much.
I wondered if you could help me with one more problem I have?
I have a cell that I want to sum only if a column has a certain text:
if b13 is Preaction then add to total
So I was thinking a vLookup on ='Budget Case Plan'!$B$34 and if it = CMC then sum the cost column as a total figure for CMC only.
Basically all the row entries with "issues" / "cmc" (16 options) and amounts associated with it to be added together and displayed in current cell. So phase of work options will show a rolling cost.
Pre-Action sum of all entries with this text (in column c row13+) from column 'I'
Issues sum of all entries with this text (in column c row13+) from column 'I'
CMC sum of all entries with this text (in column c row13+) from column 'I'
Disclosure sum of all entries with this text (in column c row13+) from column 'I'
Witness Statements sum of all entries with this text (in column c row13+) from column 'I'
Expert Reports sum of all entries with this text (in column c row13+) from column 'I'
Pre -Trial Review sum of all entries with this text (in column c row13+) from column 'I'
and there are 8 more options
Hello!
From your description, it is not entirely clear to me how your data is located.
If I understood the problem correctly, you need to sum cells by condition. You can use the SUMIF function:
=SUMIF(C13:C300,$B$13,I13:I300)
I hope it’ll be helpful.
Wonderful. Thank you.
Hi ,
How can i put unsequenced columns in Lookup array , Like A1:G7 we put but i want to use only A1,G1 then how can i do it ?
Hello!
Unfortunately, Excel search formulas can only work with one search range.
Hi, can i have formula to find the second last row (non- blank) data? Your explanation to find the last really help me a lot but could not find a formula to get second last row. Really need the formula. Thank you!
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail.
Explain in more detail, how do you understand "second last row (non- blank) data"?
Give an example of the source data and the expected result.
hello,
i want to know if we use lookup function to find value. but that value is not in searched place so it is not appear as falls or null like
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
I want a formula for Balance stock Amount in FIFO after sales value reduction in FIFO. The situation is: 1 Item Purchase in 10 pcs @ $100/Pcs, and sold 2 pcs at @$110/pcs. Now I have Balance stock of 8 Pcs @ $100/pcs. Meanwhile I Purchased additional 10 same item @ $200. Now I have Total 18 Pcs ( 8 pcs of $100/pcs & 10 Pcs of $200/pcs). Now I want the Closing Total stock Balance Amount $2800 by formula after auto deduction of sales amount by FIFO. I want the sales value should be calculated in FIFO form, First 10 items auto calculated by $100 then after 10th item the next item by $200/pcs and so on. How I can achieve this situations.