Compare values in two columns and return the value from thirdSolved/Closed

Question

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.

Kevin@Radstock · Accepted Answer

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

ramdubai · Answer

Hi Kevin,

thank you very much for the reply. That was quite useful.

However, please find below the table I was referring to.

Weld Area    Welder#	Acceptance

Shell	            W-001	        ACC
Base Pl        W-002	        REP
shell-1	    W-003	        ACC
shell-2	    W-004	        REP
shell-3	    W-001	        REP
shell-4	    W-006	        ACC
roof-1	    W-003	        ACC
roof-2	    W-005	        REP
girder-2	    W-007	        REP
girder-2	    W-008	        REP

I need to return all values in column A to a different sheet where the value in column#2="W-001" and column#3="REP" and so-on.

Thanks for your help again.

Kevin@Radstock · Answer

Hi ramdubai 

As I said in my previous post, if there is more then one value to return, then use the second formula. You will need to change the formula as in the first post you wanted to return a value from the third column. 

=IFERROR(INDEX(Sheet1!$A$2:$A$1000,SMALL(IF((Sheet1!$B$2:$B$1000="W-001")*(Sheet1!$C$2:$C$1000="REP"),ROW(Sheet1!$2:$1000)-ROW(Sheet1!$1:$1)),ROW($A1))),"") 

Array formula (CTRL + SHIFT + ENTER to commit) and copy down. You will need to adjust your columns, rows and sheet to suit your requirment. 

So with your example, there is only 1 match for "W-001" & "REP": shell-3 

Kevin

megmoto · Answer

Hi Kevin - this formula is exactly what I need in my sheet - I've replicated the above example and it works perfectly - but in my own sheet the data is located in columns B, C & F with the header rows at Row 4.  I cant figure out how I can transpose the above formula into my own sheet.  I'm guessing it is something to do with the ROW elements.

I am looking to check the criteria in C & F and then return the value in B

Could you advise?

Compare values in two columns and return the value from third

4 responses

Similar discussions

Subscribe To Our Newsletter!