If two adjacent cells in the same row are the same in tab2, return third cell value

[EDA]

Hi all,

I am trying to match the klant (column F) and bestemming (column G) in tab 1 with the values in tab "tabel". if they match the same values in the same row, I want the contract number in tab "tabel" being returned to tab 1 (tab ma). I though I could use vlookup, but it di dnot work.

Please see attachment for more details.

[TMS]

One way is with an Array function:

=INDEX('Tabel '!C:C,MATCH(F3&G3,'Tabel '!A:A&'Tabel '!B:B,0))

committed with Ctrl-Shift-Enter rather than just Enter.


Regards, TMS

[EDA]

Hi TMS, thanks.

I tried the formula, and pasted it onto cell H3 in tab ma, but received a message saying that the formula contains an error.

Is tehre a way to fix this issue?

Thanks and regards,

EDA

[TMS]

If the error is #VALUE!, you have just pressed Enter to commit the formula rather than using Ctrl-Shift-Enter as advised above.

If the formula has been entered correctly, you will see curly brackets at the beginning and end:
{=INDEX('Tabel '!C:C,MATCH(F3&G3,'Tabel '!A:A&'Tabel '!B:B,0))}

If the Error is #N/A, then the data you are looking up is not present.

Other than that, you may need to change the commas (,) into semi-colons ( for your Regional Settings ... mine are UK English.

You can test the formula by copying the values you are searching for to the tabel sheet and adding a (test) contract number, or vice versa ... copy data from the tabel sheet into the ma sheet and then use the formula.


Regards, TMS