Excel VLOOKUP where the key is not in the first column

by

INDEX/MATCH will do it any direction of search.

So for your example of B –> A:

=INDEX(A:A,MATCH(yourCriteria,B:B,0))

The MATCH returns the row number of the match. The third Criterion of 0 is optional. The 0 is the same as FALSE for the forth criterion of VLOOKUP, in that it looks for an exact match.

The default is 1 with the data sorted it will return the match that is less than or equal to the criteria Like VLOOKUP’s TRUE.

From that the INDEX finds and returns the correct value.

With the introduction of the Dynamic Array formula XLOOKUP we can use:

=XLOOKUP(yourCriteria,B:B,A:A,"",0)

More Related Contents:

  1. VLOOKUP with multiple criteria returning values in one cell
  2. Vlookup using 2 columns to reference another
  3. Comparing two columns, and returning a specific adjacent cell in Excel
  4. Excel – VLOOKUP vs. INDEX/MATCH – Which is better?
  5. Comparing two columns on sheet1 to two other columns on sheet2 and returning another column in sheet 2.
  6. Importing Excel spreadsheet data into another Excel spreadsheet containing VBA
  7. Sequence of numbers based on start and end value of 2 cells [closed]
  8. Is it possible to force Excel recognize UTF-8 CSV files automatically?
  9. Function to convert column number to letter?
  10. Better way to find last used row
  11. 2 Dimensional array from range [closed]
  12. filter out multiple criteria using excel vba
  13. Using Google Chrome in Selenium VBA (Installation Steps)
  14. Excel formula to get cell color [duplicate]
  15. Dir() function not working in Mac Excel 2011 VBA
  16. Excel VBA code to copy a specific string to clipboard
  17. How to use workbook.saveas with automatic Overwrite
  18. How to export a CSV to Excel using Powershell
  19. VBA: Convert Text to Number
  20. Referencing Excel Userform Control Name Value From String (VBA)
  21. Fill formula down till last row in column
  22. Refreshing all the pivot tables in my excel workbook with a macro
  23. Countif With Multiple OR Criteria
  24. Vlookup to copy color of a cell
  25. Macro to export MS Word tables to Excel sheets
  26. How to generate an .xlsx using php
  27. VBA WinHTTP to download file from password proteced https website
  28. sep=”;” statement breaks utf8 BOM in CSV file which is generated by XSL
  29. Write from R into template in excel while preserving formatting
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment