# Compare values in two columns and return the value from third

Solved/Closed
ramdubai Posts 2 Registration date Monday March 11, 2013 Status Member Last seen March 11, 2013 - Updated on Jan 13, 2019 at 11:28 AM
keshav - Sep 7, 2016 at 02:30 AM
Hello,

I've a table containing four columns. If the values in the first two columns match to particular value (eg. A1="xxxx" and B1="yyy"), I need to return the value in the third column to the fourth column.
Can anybody help??? Thanks in advance.
Related:

## 4 responses

Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Mar 11, 2013 at 02:57 AM
Hi ramdubai

Are the criteria values unique! If they are, try the following. assuming your data is in A1:C100 including column headers.

=LOOKUP(2,1/((A2:A1000=Value1)*(B2:B1000=Value2)),C2:C1000)

If there are multiple criteria which match, then try the array formula ("ctrl + shift + enter" to commit)

=IFERROR(INDEX(\$C\$2:\$C\$1000,SMALL(IF((\$A\$2:\$A\$1000=Value1)*(\$B\$2:\$B\$1000=Value2),ROW(\$2:\$1000)-ROW(\$1:\$1)),ROW(\$A1))),"")

Kevin