1

I need a little help here.

I always use the match and index functions in excel just like an example below:

Table "DataRange" is the range of the data, i.e. D8:G12

Cell A3 and Cell B3 are the headers;
Cell A4 and Cell A5 are the "match and index" keys;
And Cell B4 and Cell B5 are the results.

For example, if I change the Cell B3 from "Shop_Name" to "Status", the values of Cell B4 and Cells B5 will be "Same" and "New" respectively.

I would like to ask anyone can convert this fomula to a tailor-madeVBA function? Then I can shorter my formula like "=matchindex(DataRange,$A4,B$3)" and generate the same result "Shop_B"

Example
Example

Sunil
  • 3,404
  • 10
  • 23
  • 31
learnerben
  • 11
  • 1
  • 2
    I wouldn't recommend turning Excel formula combinations into VBA UDFS, because UDFs recalculate in response to a whole bunch of things, as outlined in my question at https://stackoverflow.com/questions/29815150/need-to-stop-udfs-recalculating-when-unrelated-cells-deleted and this could be particularly problematic on UDF-based lookup formulas if they reference large ranges...could make your spreadsheets very sluggish for end users. – jeffreyweir Apr 12 '18 at 04:17

0 Answers0