excelexcel-formulaexcel-match
Two column lookup in table array using INDEX and MATCH
I would like excel to display the value from table array which has two matching cells com_cd and div_cd using INDEX and MATCH.
I have tried the following formula but it did not work.
=INDEX(K9:K53,MATCH(K3,I9:I53,0),MATCH(K4,J9:J53,0))
Here is a screenshot of the excel sheet with the desired result given according to com_cd and div_cd
Solution
Try an array formula (CTRL + SHIFT + ENTER) instead of Enter.
=INDEX(K9:K53,MATCH(K3&K4,I9:I53&J9:J53,0),1)
Not tested but should work.
Will edit later explaining our formula and reason why your formula doesn't work.
- Sortby function combined with Unique and filter functions: how to use it correclty?
- Count of Unique Values based on who it's assigned to?
- Excel ActiveSheet.Shapes.Range(MyArray).Select
- How to color excel cells with specific values in dataframe Python
- Talend import, transform a raw file and export to CSV
- How to change Font Color of dates without changing color of other text or background using VBA for Excel
- Auto Size Text To Fit Shape via vba
- How to recolor dates without recoloring other text or numbers
- Send Keys is Disabling NumLock
- Use VBA to Clear Immediate Window?
- Extract the first and last entries for each day
- using XLOOKUP to find matching names over multiple tabs and supplying data from specific cells as a result
- PowerPoint VBA code is inconsistent when running in attempt to export OLE objects as high resolution JPG files
- Convert 2-D array to 1-D array & paste 1-D array to another sheet
- I dont want to extract data of chart from excel cells
- VBA Autofilter Using Multiple Criteria
- Save entries from one sheet to another sheet
- How to make Uppercase Month Names in Y axes of the Chart via vba?
- Fill PDF form data from Excel with Adobe Acrobat Pro
- Webscraping dynamic content using Excel VBA
- I am having Zip Code Replacement Issues Excel VBA
- Handle multiple text boxes together
- Excel VBA If range.value = something then
- Openpyxl How to get row from worksheet by index
- Are there any performance advantages to use `Application.Match` on a VBA array instead of looping over it to check if an element is present or not?
- Import recordset from Access to Excel ListBox
- Sum and subtract among column and row cells in excel
- Call one of two templates depending on a variable
- Chart range based on the value in a cell
- Excel VBA Union Delete Columns Quickly Producing Error 1004