Excel - Search for name & return column value

March 2017


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


Published by aakai1056.
This document, titled "Excel - Search for name & return column value," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).