CODEWITHSUNDEEP
excelexcel-2010formulapivot-tablelookup
Kairan
Kairan

Reputation: 5542

Formula to find matching row value based on cells in multiple columns

I am trying to lookup a col4 value when, in the same row, col1, col2 and col3 each match.

I referenced this SO Post as it is a similar question but that solution is not working for me.

I am searching for specific Sources, on specific Dates, with specific Statuss and want to return the corresponding Counts.

My data with examples of output required:

ReuploadedImage

Upvotes: 4

Views: 34861

Answers (2)

pnuts
pnuts

Reputation: 59485

May not be preferred approach and might not even give the correct answer but for anyone new to PivotTables the sooner they are comfortable with them the more effort they are likely to save themselves:

SO19433337 example

Select your dataset (A1:D16 above), Insert > Tables – PivotTable, PivotTable. Choose New Worksheet or, as here, Existing Worksheet Location F1. Drag the fields from the upper box into the smaller boxes as shown. The rest is just formatting, unless complicated by specific requirements for treatment of Src All.

Upvotes: 1

Mike Drawback
Mike Drawback

Reputation: 626

To use INDEX() and MATCH() with multiple lookup fields, you must commit it as an array formula. Use nested IF() statements to select for each criteria. You have to push CTRL+SHIFT+ENTER to enter a formula as an array formula.

=INDEX(col4,
    MATCH(criteria1,
        IF(col2=criteria2,
        IF(col3=criteria3,
    col1
        )), 
    0)
)

If you entered it correctly with CTRL+SHIFT+ENTER, you will see curly braces around the formula { }

Also see this article from Microsoft

Upvotes: 4

Related Questions