Excel: match two columns and output third … AND… there are multiple instances in each column

by

You can retrieve a two column match using the AGGREGATE function to force anything that does not match into an error and ignore the errors.

      INDEX AGGREGATE with two column MATCH

The formula in E6 is,

=IFERROR(INDEX(C$1:C$99,AGGREGATE(15,6,ROW($1:$99)/((A$1:A$99="red")*(B$1:B$99="boat")), ROW(1:1))), "")

You are actually using the SMALL sub-function of the AGGREGATE function so you can get the second, third, etc. successive matches by increasing the k paramter. I done this above by using ROW(1:1) which equals 1 but will increase to 2, 3, etc as the formula is filled down.

More Related Contents:

  1. SumIf using Excel VBA
  2. Test or check if sheet exists
  3. How to turn a string formula into a “real” formula?
  4. Copy from one workbook and paste into another
  5. How to copy Outlook mail message into excel using VBA or Macros
  6. Read an Excel file directly from a R script
  7. For Loop not fully cycling in excel VBA [duplicate]
  8. How to loop through Excel files and load them into a database using SSIS package?
  9. Unique Random Numbers using VBA
  10. Populate unique values into a VBA array from Excel
  11. Excel VBA App stops spontaneously with message “Code execution has been halted”
  12. How to remove all non alphanumeric characters from a string except period and space in excel?
  13. Retrieving the COM class factory for component failed
  14. Closing Excel Application using VBA
  15. How to clear memory to prevent “out of memory error” in excel vba?
  16. What are the benefits of setting objects to “Nothing”
  17. How can I kill task manager processes through VBA code?
  18. VBA: Usage of parentheses for a method
  19. Fully reference a worksheet by codename
  20. Extract Data from Word Document to an Excel SpreadSheet
  21. Excel: Use a cell value as a parameter for a SQL query
  22. VBA equivalent to Excel’s mod function
  23. Convert from US to UK format
  24. Pointers needed for speeding up nested loop macro in VBA
  25. Comparing two columns on sheet1 to two other columns on sheet2 and returning another column in sheet 2.
  26. ‘1004’: “The sort reference is not valid.”
  27. Select entire column in table using Excel VBA
  28. Reducing WithEvent declarations and subs with VBA and ActiveX
  29. Function Overloading and UDF in Excel VBA
  30. VBA Excel simple Error Handling

Leave a Comment