Search code examples
excelexcel-formulavlookupexcel-2013

nested vlookup in excel 2013

I need to search a spreadsheet for multiple criteria.

enter image description here

Say that I want to pull the batch # with given criteria from the 837 file and type of file. For instance if the values I am searching for are 2456 and 999 I want to get 2499. How would I create a formula for this? I tried to use an index and match formula but could not get that to work.

I tried to concantenate but the value is pulling incorrectly.

enter image description here

An Index/Match Formula pulls #N/A

enter image description here

Solution

  • If the pairs of 837File and type of file are unique then a simple SUMIFS function will suffice.

    =sumifs(C:C, A:A, 2456, B:B, 999)
'or with 2456 in Y2 and 999 in Z2 as,
=sumifs(C:C, A:A, Y2, B:B, Z2)

    If the the pairs of 837File and type of file are not unique then you need to specify how you want to handle multiple returns.