SOLVED

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

Copper 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
best response confirmed by krisi042 (Copper 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

@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"?

:)

 

I suggest you store additional search text in another cell.
1 best response

Accepted Solutions
best response confirmed by krisi042 (Copper 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

View solution in original post