Dear All,

I use the IndexMatch function to get data from an excel sheet ("Data"). According to my workbook, I hope to get data to the "B5" Cell in the "Calculation" Sheet matching "B1" & "B3" Cell values from the "Data" Sheet. I try to get only Year & Month from B3 Cell to match with column "A" in the "Data" Sheet. I use the below formula but not working correctly.

=INDEX(Data!$D:$D,MATCH(1,INDEX((B1=Data!$B:$B)*(B 3=Data!$A:$A),0,1),0))

can anyone help me?



Thanks

Priyantha.

Attached Files

Increment.xlsx (15.3 KB, 5 views)

And what is the expected answer in B5 ?

(BTW using entire column/row referencing is bad practice)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

maybe:?

I hope to get paid salary for the increment date ( if emloyee incremen date is 2022/10/20, then hope to get 2022 october month paid salary)


Thanks

This code works after modifying it as below.

=INDEX(Data!$D$2:$D$13,MATCH(1,INDEX((YEAR(Data!$A $2:$A$13)=YEAR($B$3))*(MONTH(Data!$A$2:$A$13)=MONT H($B$3))*(Data!$B$2:$B$13=$B$1),0,1),0))


Thank..

That's crazy! Are you using Excel? What version?
Indexing twice is pointless:
2023-01-08_123457.png

Much easier and more flexible way
Define fields Calculation!$B$1 and Calculation!$B$5 as Names, like nPFNo and nDate - the same formula will be
Define the Table on sheet Data as Defined Table, eg. tData, and the same formula will be
Replace column header [PF No] in tData Table with PF, and check out the formula in Table syntax for Salary in Calculation Sheet - you see the formula was changed properly too!

From:
I took it that datewise, the OP wanted only a month and year match, so that dates didn't need to be the first of the month everywhere.

All OP's month values in Data sheet were 1st of month, so it was logical to assume this is a part of routine. In case it is not the case, OP can have a calculated column which returns the 1st of month based on date in column Month in SUMIFS formula. Or OP can redesign the table, and use instead date values for months integer values in format yyyymm.

OP can also take some 15 years back (Into time when there wasn't any SUMIFS/COUNTIFS functions in Excel, and SUMPRODUCT was used instead - with a bonus that you could refer to calculations instead columns)

Thanks for all of you