SOLVED

Check column for specific value, and if its found, copy the value of the previous cell

%3CLINGO-SUB%20id%3D%22lingo-sub-958276%22%20slang%3D%22en-US%22%3ECheck%20column%20for%20specific%20value%2C%20and%20if%20its%20found%2C%20copy%20the%20value%20of%20the%20previous%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-958276%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%60m%20doing%20some%20expenses%20calculation%20and%20I%20was%20wondering%20if%20there%20is%20a%20way%20to%20search%20for%20specific%20text%20in%20a%20column%20and%20if%20it%20finds%20that%20text%20to%20copy%20the%20value%20of%20the%20previous%20cell%20to%20the%20selected%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Example%20for%20the%20phone%20bill%20I%20would%20like%20to%20search%20column%20J%20for%20text%20%22A1%22%20and%20if%20there%20is%20such%20cell%20to%20copy%20the%20value%20of%20the%20previous%20cell%20which%20is%20%2210%22%20to%20the%20one%20I%20need%20-%20C4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F151471i1A976E391688CFE0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3EI%20saw%20something%20similar%20in%20the%20below%20thread%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FHOW-TO-quot-If-cell-contains-specific-text-then-return-specific%2Fm-p%2F958263%23M42934%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FHOW-TO-quot-If-cell-contains-specific-text-then-return-specific%2Fm-p%2F958263%23M42934%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-958276%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-958816%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20column%20for%20specific%20value%2C%20and%20if%20its%20found%2C%20copy%20the%20value%20of%20the%20previous%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-958816%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F434576%22%20target%3D%22_blank%22%3E%40krisi042%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20store%20your%20search%20text%20in%20A4%2C%20then%20enter%20this%20formula%20in%20C4%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(2%2C1%2F(%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EISNUMBER(SEARCH(A4%2CJ%246%3AJ%2412)))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EI%246%3AI%2412)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20foregoing%20formula%20shall%20return%2010%2C%20as%20shown%20below%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20537px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F151477iB91FA7D4156EB831%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Search%20Lookup.PNG%22%20title%3D%22Search%20Lookup.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-958931%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20column%20for%20specific%20value%2C%20and%20if%20its%20found%2C%20copy%20the%20value%20of%20the%20previous%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-958931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Bit%20works%20very%20well%20for%20me%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Thank%20you%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20addition%20do%20you%20think%20I%20can%20add%20multiple%20values%20in%20A4%20cell%3F%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20example%20%22A1%22%3B%20%22A2%22%3F%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-958961%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20column%20for%20specific%20value%2C%20and%20if%20its%20found%2C%20copy%20the%20value%20of%20the%20previous%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-958961%22%20slang%3D%22en-US%22%3EI%20suggest%20you%20store%20additional%20search%20text%20in%20another%20cell.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-959025%22%20slang%3D%22en-US%22%3ERe%3A%20Check%20column%20for%20specific%20value%2C%20and%20if%20its%20found%2C%20copy%20the%20value%20of%20the%20previous%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-959025%22%20slang%3D%22en-US%22%3EI%20did%20that%20but%20it%20is%20not%20sum-ing%20them%20at%20the%20end.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, 

 

I`m doing some expenses calculation and I was wondering if there is a way to search for specific text in a column and if it finds that text to copy the value of the previous cell to the selected one.

 

For Example for the phone bill I would like to search column J for text "A1" and if there is such cell to copy the value of the previous cell which is "10" to the one I need - C4

 

clipboard_image_0.png
I saw something similar in the below thread: 

https://techcommunity.microsoft.com/t5/Excel/HOW-TO-quot-If-cell-contains-specific-text-then-return-...

 

Could you please help? 

 

3 Replies
Highlighted
Best Response confirmed by krisi042 (New Contributor)
Solution

@krisi042 

You may store your search text in A4, then enter this formula in C4: 

=LOOKUP(2,1/(
ISNUMBER(SEARCH(A4,J$6:J$12))),
I$6:I$12)

The foregoing formula shall return 10, as shown below: 

Search Lookup.PNG

Highlighted

@Twifoo it works very well for me Thank you .

 

In addition do you think I can add multiple values in A4 cell? 

for example "A1"; "A2"?

 

Highlighted
I suggest you store additional search text in another cell.