Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Excel sessions – 2013 VS 2010 Excel User tips
  • Return 12 values in one number AND
  • The Excel “&” bug Excel User tips
  • Week number to Sunday date – Weeknum reverse DATE
  • Repeated Offset Basic Math
  • Compare2, a tool to compare two sheets using formulas Conditional Formatting
  • Middle name + 1st name Basic Math
  • Min Max vehicle Formulas - combined functions

OFFSET+MATCH

Posted on June 9, 2017 By ANmar

One of the power of combining functions in one cell is using the best of these functions

An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.

Why do we want to do that?

VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time

We needed several times to locate a row by using a column on the right, then get the value from a column left to it.

This is when VLOOKUP can not help us.

And this is when a combined function like OFFSET + MATCH can get the job done.

So, if we have a table as below

A simple VLOOKUP can help us find any of these values, say “Test_Country”

=VLOOKUP("Test_Country",AB:AC,2,FALSE)

Which will return the expected result.

However, a table as below, we cannot use VLOOKUP if we want to search using “TreeKey” column, or “UnitID”, basically any column on the right of the table

Since VLOOKUP searches only left-most column of table.

And if we try using -ve number in “Col_index_num” we get an error

 

So, what can we do?

Use combination of OFFSET and MATCH

MATCH will do the actual search in the needed column, while OFFSET gives me the actual return as below

below example will search for “T1_3_1_9” in column “TreeKey” column and returns the “PVID” of that item

=OFFSET($DM$1,MATCH("T1_3_1_9",DR:DR,0),2)

Or, we can search for “FC12_1B500733244144” in “UnitID” column and return its “PVID” as below

=OFFSET($DM$1,MATCH("FC12_1B500733244144",DP:DP,0),2)

Hope this will help

Formulas - combined functions, Lookup and References, MATCH, OFFSET, Standard functions Tags:MATCH, OFFSET, VLOOKUP

Post navigation

Previous Post: Excel sessions – 2013 VS 2010
Next Post: New function IFERROR, finally

Related Posts

  • Scorring, saving multiple outputs in 1 number Basic Math
  • Count Unique COUNTIF
  • Sheet() and Sheets() Lookup and References
  • Get 3rd Wednesday of month Basic Math
  • Extract current (Active) workbook name CELL
  • Tally chart in functions Formulas - combined functions

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Shades of gray Graphics
  • Text-to-Columns, dynamically using formulas IFERROR
  • Count cells with condition – multiple conditions COUNTIFS
  • VALUE() of LEFT() Formulas - combined functions
  • Jan8 date format AND
  • LastSunday, LastSaturday Date and Time
  • WorksheetName and WorksheetsNo UDFs Lookup and References
  • Skills Grid Conditional Formatting

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme