Excel - Search for name & return column value

December 2016


I. I have 2 sheets sheet 1&2, on Sht1 is cells J2 & K2 where a name can be put in. On sht2-B2 I got a name eg "SAM".I have put a formula in Sht2-B3 that look at Sht1-J2 and if the name SAM appear it SUM J3:J28*1000%.FORMULA used:=IF('Sheet1'!J2=Sheet2!B2,1,SUM('Sheet1'!J3:J28)*1000%). I am trying to double it up to say look in SHt1-J2 and K2 and if "SAM" appear in Sht1-K2 SUM K2:K28*1000% and when "SAM" is in Sht1-J2 SUM J3:J28*1000%.


  • Try this:

=IF(B2=Sheet1!K2, SUM(K2:K28)*1000%, IF(B2=Sheet1!J2, SUM(J2:J28)*1000%, "")) 


Solved by rizvisa1

Related :

This document entitled « Excel - Search for name & return column value » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.