- To post as a guest, your comment is unpublished.· 7 months agoHi Niel,
The method in this article can do you a favor https://www.extendoffice.com/documents/excel/2623-excel-vlookup-return-multiple-values-horizontally.html.
Thank you for your comment.
How to vlookup return value in adjacent or next cell in Excel?
How can we vlookup a value but to find the value in adjacent or next cell? In this article, we will guide you through how to vlookup a value and return value in adjacent or next cell in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
For example, you have two columns as shown in following screenshot. To vlookup the largest weight in column A and return the adjacent cell value of column B, please do as follows.
1. Select a blank cell (here I select cell C3), copy and paste the formula =VLOOKUP(MAX(A2:A11), A2:B11, 2, FALSE) into the Formula Bar, and then press the Enter key. See screenshot:
Then the adjacent cell value of the largest weight in column A is populated in the selected cell.
Besides, you can vlookup and get the adjacent cell value based on a cell reference.
For example, the referenced cell is C2 which locates value 365, for vlookup this value and return the value in adjacent cell. You just need to change the above formula to =VLOOKUP(C2, A2:B11, 2, FALSE), and then press the Enter key. See screenshot:
This Index function also works: =INDEX($B$1:$B$7, SMALL(IF(ISNUMBER(SEARCH($C$2, $A$1:$A$7)), MATCH(ROW($A$1:$A$7), ROW($A$1:$A$7))), ROW(A1))) + Ctrl + Shift + Enter.
Besides returning value in an adjacent cell, you can vlookup and return value in the next cell of the adjacent cell in Excel. See screenshot:
1. Select a blank cell, copy and paste formula =INDEX(B2:B7,MATCH(680,A2:A7,0)+1) into the Formula Bar, and then press the Enter key. See screenshot:
Then you can see the value in next cell is populated into the selected cell.
Note: 680 in the formula is the value which I vlookup in this case, you can change it as you want.
With the Look for a value in list utility of Kutools for Excel, you can vlookup and return value in adjacent cell without formulas.
1. Select a blank cell for locating the adjacent cell value. Then click Kutools > Formula Helper > Look for a value in List. See screenshot:
2. In the Formula Helper dialog box, please do as follows:
1). Click the button in the Table_array box, and then select the range contains the vlookup value and the adjacent value you will return (in this case, we select range A2:C10).
2). Click the button in the Look_value box, and then select the cell which you will vlookup and return its adjacent cell value (In this case, we select cell E2).
3). In the Column box, click the button to select the column range which the adjacent cell locates in.
4). Click the OK button. See screenshot:
Then you will get the adjacent cell value of cell A8.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 months agoHow can you add if there are multiple items on the list