combination of if, countif and index functions

[bradhakrishan]

Hi,

I am having trouble with getting a value from a column, this value has to be fetched by combining two columns to avoid duplicates

for example
excel.JPG

The column A has to be combo of column B and C

The sheet name is Source_LU

I tried with this formula,

=IF(COUNTIF('Source LU'!B:C:D,'DS From ERS'!L2:M2),INDEX('Source LU'!A:A,MATCH('DS From ERS'!L2:M2,'Source LU'!B:C:D,0)),"NOT FOUND")

And in sheet DS from ERS I want to match values in column L and M, any ideas on how to approach this ?

Edit 1 : I have uploaded the excel, There are two sheets to be looked at, DS from ERS and Source LU

I want to get the ID in the source LU which is in A column
combination of Column B and C in Source Lu should match with column L and M in DS from ERS

[Jacc]

If you attach a workbook (not pictures) with some data and the expected output it will be much easier to help you.

[bradhakrishan]

Hi,

I have added the excel file

[JeteMc]

Hello bradhakrishan and Welcome to Excel Forum.
I am confused as to where the formula is supposed to be applied. Please tell us the sheet and cell that should be populated using the formula as well as the expected output. If it is not very obvious as to why the output is expected, then please include an explanation.
That said the formula needs to be modified as ...'Source LU'!B:C:D... will not work. If the intention is to reference columns B, C and D then the syntax would be 'Source LU'!B:D. I am not suggesting that will resolve the issue just making an observation.
Let us know if you have any questions.