Index Match Return Values

[Justijb]

Hello All,

Thanks for all the support in the past. I would like to use the following formula =INDEX(Sheet2!C:C,MATCH(1,(N2=Sheet2!B:B)*(A2=Sheet2!A:A),0) )but it seems to not function properly.

I have two worksheets open 1 and 2 = I have two criteria to match on. Column A and Column N on sheet 1. If column (a) and column N on sheet 1 matches to column (a) and column (b) on sheet 2, then return value from column (c) sheet 2, to sheet 1 column (o).

IF

Column (a) /sheet 1 matches column (a) sheet 2, but column N - DOES NOT MATCH or meet criteria of specified letters word in column "x,y,z" return sheet 2 column (e,f,g,).

Essentially I want to match on two criteria - 1 function/ return value..simple enough, 2nd function is if there is one match on criteria one but a different match on criteria 2, use a different set of data in a different column.

I can explain further if needed. Thanks again and appreciate all of you !

 

[FatBoyClam]

The formula you've written looks like it should meet your initial needs i.e. it will return from Column C on Sheet2 where the values from A and N on Sheet1 are found in B and A on Sheet2. If it's not working, check you're entering it as an array formula by editing it in the formula bar and pressing Ctrl+Shift+Enter to commit the formula. When done you should see the formula in curly braces like this;

I don't understand the second part of your request though.

Specifically this bit:

"meet criteria of specified letters word in column "x,y,z" return sheet 2 column (e,f,g,). "

Can you clarify?

 

[Justijb]

Thanks for the reply! The second part of the formula would be if column a is true and column n is not true that it would return a different value from altogether different column in sheet 2 to sheet one column o. To be more exact code and state don't match so it looks up a different value and returns a location I specify.

 

[FatBoyClam]

OK.

Then do this (still entered as an array formula):

=IFERROR(INDEX(Sheet2!C:C,MATCH(1,(N2=Sheet2!B:B)*(A2=Sheet2!A:A),0) ) ,INDEX(Sheet2![Column Name here],MATCH(A2,Sheet2!A:A,0) )

You need to replace [Column Name here] with the column in Sheet2 you want to return the value from

The Index/Match pair you already have will return an error if the two values are not found on the same row in columns B and A.

The IFERROR will return the results from the second Index/Match pair which is only looking for the value from Sheet1!A2 in Sheet2!A:A

Note: if the value from Sheet1 is not found in Sheet2, this will still result in a #N/A error.