Look Up Values With VLOOKUP, INDEX, Or MATCH - Microsoft Support
Có thể bạn quan tâm
INDEX and MATCH Examples
This last example employs the INDEX and MATCH functions together to return the earliest invoice number and its corresponding date for each of five cities. Because the date is returned as a number, we use the TEXT function to format it as a date. The INDEX function actually uses the result of the MATCH function as its argument. The combination of the INDEX and MATCH functions are used twice in each formula – first, to return the invoice number, and then to return the date.
Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel.
Tip: Before you paste the data into Excel, set the column widths for columns A through D to 250 pixels, and click Wrap Text (Home tab, Alignment group).
| Invoice | City | Invoice Date | Earliest invoice by city, with date |
|---|---|---|---|
| 3115 | Atlanta | 4/7/12 | ="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/yy") |
| 3137 | Atlanta | 4/9/12 | ="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/yy") |
| 3154 | Atlanta | 4/11/12 | ="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/yy") |
| 3191 | Atlanta | 4/21/12 | ="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/yy") |
| 3293 | Atlanta | 4/25/12 | ="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy") |
| 3331 | Atlanta | 4/27/12 | |
| 3350 | Atlanta | 4/28/12 | |
| 3390 | Atlanta | 5/1/12 | |
| 3441 | Atlanta | 5/2/12 | |
| 3517 | Atlanta | 5/8/12 | |
| 3124 | Austin | 4/9/12 | |
| 3155 | Austin | 4/11/12 | |
| 3177 | Austin | 4/19/12 | |
| 3357 | Austin | 4/28/12 | |
| 3492 | Austin | 5/6/12 | |
| 3316 | Dallas | 4/25/12 | |
| 3346 | Dallas | 4/28/12 | |
| 3372 | Dallas | 5/1/12 | |
| 3414 | Dallas | 5/1/12 | |
| 3451 | Dallas | 5/2/12 | |
| 3467 | Dallas | 5/2/12 | |
| 3474 | Dallas | 5/4/12 | |
| 3490 | Dallas | 5/5/12 | |
| 3503 | Dallas | 5/8/12 | |
| 3151 | New Orleans | 4/9/12 | |
| 3438 | New Orleans | 5/2/12 | |
| 3471 | New Orleans | 5/4/12 | |
| 3160 | Tampa | 4/18/12 | |
| 3328 | Tampa | 4/26/12 | |
| 3368 | Tampa | 4/29/12 | |
| 3420 | Tampa | 5/1/12 | |
| 3501 | Tampa | 5/6/12 |
Từ khóa » How To Use Vlookup From Another Sheet In Excel
-
Excel VLOOKUP Function Tutorial With Formula Examples
-
Excel Formula: VLOOKUP From Another Sheet - Exceljet
-
VLOOKUP Across Multiple Sheets In Excel With Examples
-
EXCEL VLOOKUP: How To Lookup Data From Another Sheet
-
VLOOKUP From Another Sheet / Workbook - WallStreetMojo
-
How To VLOOKUP Another Sheet In Excel | Blog
-
Excel Formula: VLOOKUP From Another Sheet - Excelchat - Got It AI
-
Excel VLOOKUP From Another Sheet - EduCBA
-
How To VLOOKUP From Another Sheet? - Simon Sez IT
-
Excel VLookup: Combining 2 Spreadsheets
-
How To Use VLookup With Different Sheets On Excel
-
Excel VLOOKUP From Another Workbook - Contextures Blog
-
How To Use VLOOKUP In Excel - Laptop Mag