Excel function with multiple arguments to return a result

Question

I have the below function to compare  many argument and return a value in cell.

Example:

The values in ColumnH are equal with the value in column J2 and in column A contain Past, Present or Future and return the value from column A in column L.

Column A         Column H        Column J    Results Column L

Past                       Italy                Italy                   Past

Past                       Italy               China                  Present

Present                 China              Spain                  Future

Present                 China

Future                   Spain

I Try to use this function but not work:

=IF(ISTEXT(MATCH(J2,H:H,0)),VLOOKUP(J2,A:H,1,0),"")

Answer 1

Hi,

Try this

=INDEX($A$2:$A$6,MATCH(1,INDEX(($H$2:$H$6=J2)*($E$2:$E$6="Yes"),,),0),1)

Hope this helps.

Answer 2

Hi,

Try this formula in cell L2 and copy down

=INDEX($A$2:$A$6,MATCH(J2,$H$2:$H$6,0),1)

Hope this helps.

Answer 3

Hi Ashish,

The both codes work perfect.

Thanks,

Magia

Answer 4

Hi,

I try to add another condition in the function but not work correctly with this condition. Help

=INDEX(A:A,MATCH(J2,H:H,E:E="YES"),1)