Three Dimensional Lookup Using INDEX/MATCH

by

I used an IF() statement array formula to find what the P row number was after the George row… I also needed to use the MIN() function to get the first P row number after the name.

Beyond that, it’s a simple INDEX() function…. that racked my brain for over an hour :).

=INDEX($A$1:$D$9,MIN(IF((ROW(A1:A9)>MATCH($F$4,A1:A9,0))*(A1:A9=$F$5),ROW(A1:A9),"")),MATCH($F$6,$A$1:$D$1,0))

Don’t Forget!
Use Ctrl+Shift+Enter when finishing the formula, so it gets evaluated as an array formula.

3 Dimensional Array Function

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. How to convert to percentage [closed]
  3. Excel Number Separation [closed]
  4. Excel formula checklist in employees
  5. I have an Excel data sheet that I would like to extract data from into a new sheet
  6. Getting unique values in Excel by using formulas only
  7. Return empty cell from formula in Excel
  8. Excel: last character/string match in a string
  9. Referencing value in a closed Excel workbook using INDIRECT?
  10. Simple Pivot Table to Count Unique Values
  11. How can I perform a reverse string search in Excel without using VBA?
  12. Excel UNIQUE Across Columns
  13. Filling any empty cells with the value above
  14. How to create a string or formula containing double quotes in Excel?
  15. Find last used cell in Excel VBA
  16. Remove leading or trailing spaces in an entire column of data
  17. Count Unique values with a condition
  18. Excel Date to String conversion
  19. Excel: Formulas for converting data among column / row / matrix
  20. Excel: Searching for multiple terms in a cell
  21. How to list all possible combinations of the values in three columns in excel?
  22. How to split texts from dynamic range?
  23. Excel: Search for a list of strings within a particular string using array formulas?
  24. Convert from US to UK format
  25. How to extract text within a string of text
  26. excel vlookup with multiple results
  27. Date-Formatting in TEXT formula independent from used language in Excel
  28. Check whether a cell contains a substring
  29. Unique Rank value for a subgroup within a group
  30. Get content of a cell given the row and column numbers

Leave a Comment