Help finding a formula in Excel

[marcinjudzinski]

I'm looking for a formula that allows me to find a result if column C "Num. Aziendale" and column F "Data" of the first screenshot

correspond to column M "Vehicle number" and column H (Def. date) of the second screenshot

if they match, I need to display the data in column A "Claim" of the second screenshot. I tried to set the IF AND formula but I don't get the desired result.

thanks in advance for the help.

[#2]

@marcinjudzinski 

Let's say the sheet in the second screenshot is named Sheet 2.

In the cell in row 2 of the sheet in the first screenshot where you want the result:

=IFERROR(INDEX('Sheet 2'!$A$2:$A$1000, MATCH(1, ('Sheet 2'!$M$2:$M$1000=C2)*('Sheet 2'!$H$2:$H$1000=F2), 0)), "")

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

Then fill or copy down.

[#3]

@Hans Vogelaar 

I tried to copy your formula (the names of the formulas are in Italian but they are those indicated by you), however the error appears on this part:

[#4]

@marcinjudzinski 

If you use comma as decimal separator (for example 3,14), you should use semicolon ; instead of comma , in the formula.

[#5]

@Hans Vogelaar 

ok

; make the formula work but the result is always ""

I can't understand where I'm wrong :(

[#6]

@marcinjudzinski 

Could you attach a sample workbook with the formula, or make it available through for example OneDrive?

[#7]

@Hans Vogelaar 

I am attaching an example of a file, once I find the right formula I will use it on the general one with the exact same layout.

in practice: if C & F of Sheet1 are present in F & L of Sheet2, in P of Sheet1 I must have A of Sheet2.

[#8]

@Hans Vogelaar 

I hope I was clear

[#9]

@marcinjudzinski 

I was away from home, so I couldn't reply immediately.

You didn't include the formula in your workbook!

See column P on Foglio1.

[#10]

@Hans Vogelaar 

you are right! unfortunately at the moment I don't have my laptop with me and I don't have access to the file. could you try to write the formula according to your knowledge and see what result you get?

[#11 · Accepted solution]

@marcinjudzinski 

In English:

=IFERROR(INDEX(Foglio2!$A$2:$A$34890, MATCH(1, (Foglio2!$L$2:$L$34890=C2)*(Foglio2!$F$2:$F$34890=F2), 0)), "")

In Italian:

=SE.ERRORE(INDICE(Foglio2!$A$2:$A$34890; CONFRONTA(1; (Foglio2!$L$2:$L$34890=C2)*(Foglio2!$F$2:$F$34890=F2); 0)); "")

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.