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

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

I saw something similar in the below thread:

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

Could you please help?

test (2).xlsx11 KB

excel

Formulas and Functions

Twifoo
Oct 28, 2019
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:
SearchLookup.xlsx12 KB

3 Replies

Twifoo
Silver Contributor
Oct 28, 2019
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:
SearchLookup.xlsx12 KB
- krisi042
  Copper Contributor
  Oct 28, 2019
  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"?
  🙂
  - Twifoo
    Silver Contributor
    Oct 28, 2019
    I suggest you store additional search text in another cell.

Forum Discussion

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

3 Replies

Resources