If cell contains text then return value in another cell...apply to mutiple cells at once
By irene on Friday, 15 July 2022
Posted in Kutools for Excel
Replies 10
Likes 1
Views 5.5K
Votes 0
Hi!

I'm trying to apply this → https://www.extendoffice.com/documents/excel/3730-excel-if-cell-contains-text-then-return-value-in-another-cell.html#kutools to multiple cells and I don't want to do it one by one. Any suggestions?
Hi there,

After you apply the formula to a cell, you can drag the fill handle to fill the formula to other cells: https://support.microsoft.com/en-us/office/fill-a-formula-down-into-adjacent-cells-041edfe2-05bc-40e6-b933-ef48c3f308c6

Amanda
Amanda Lee
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
Hi,

Thanks for the advice. Unfortunately, that doesn't work. I get a "N/A".
irene
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
Hi there,

Can you show me screenshots of your data?

Amanda
Amanda Lee
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
Quoted from Amanda Lee on Monday, 18 July 2022
Hi there,

Can you show me screenshots of your data?

Amanda





I'm trying to create a meal planner. What I want to happen is when I select a food item, the calories, protein, carbs, and fat content automatically fill in.
The first picture is where I'm trying to apply the function. The function worked for the column that says "54" but when I drag it to the right, I get "N/A"
The second picture is the table range that contains the lookup value and the result value.
irene
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
Hi there,

From the second picture, I don't see calories, protein, carbs, and fat values for the foods besides Bacond, cooked, so you cannot lookup the values for other foods in the green picture.

To get protein, carbs, and fat values for Bacond, cooked, you should make some of the cell references in the formula absolute by add the dollar sign ($). If you don't understand, please copy the formula in the intersection of Cal and Bacon, cooked.

And please, when you attach a picture, make sure the picture covers row numbers, column letters and formula bar as shown below:
picture.png


Amanda
Amanda Lee
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
Quoted from Amanda Lee on Tuesday, 19 July 2022
Hi there,

From the second picture, I don't see calories, protein, carbs, and fat values for the foods besides Bacond, cooked, so you cannot lookup the values for other foods in the green picture.

To get protein, carbs, and fat values for Bacond, cooked, you should make some of the cell references in the formula absolute by add the dollar sign ($). If you don't understand, please copy the formula in the intersection of Cal and Bacon, cooked.

And please, when you attach a picture, make sure the picture covers row numbers, column letters and formula bar as shown below:
[attachment]picture.png[/attachment]

Amanda


When I try to copy the formula, I get this error message: This value doesn't match the validation restrictions defined for this cell

Here are the screencaps with the rows and column numbers and letters:



irene
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·


irene
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
Quoted from Amanda Lee on Tuesday, 19 July 2022
Hi there,

From the second picture, I don't see calories, protein, carbs, and fat values for the foods besides Bacond, cooked, so you cannot lookup the values for other foods in the green picture.

To get protein, carbs, and fat values for Bacond, cooked, you should make some of the cell references in the formula absolute by add the dollar sign ($). If you don't understand, please copy the formula in the intersection of Cal and Bacon, cooked.

And please, when you attach a picture, make sure the picture covers row numbers, column letters and formula bar as shown below:
[attachment]picture.png[/attachment]

Amanda


I also get the error message This value doesn't match the validation restrictions defined for this cell when I try to add dollar signs to the formula.
irene
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
Hi, you should have applied validation restrictions on your cells. Follow the steps to clear them:
  1. Select the cell or cells that you want to remove validation from.
  2. Click Data > Data Tools > Data Validation.
  3. Click the Clear All button.
  4. Click OK.

Then please copy the formula in the cell D5 in the green table. If you cannot, take a screenshot of THE FORMULA IN D5.
Thanks.

Amanda
Amanda Lee
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
Quoted from Amanda Lee on Wednesday, 20 July 2022
Hi, you should have applied validation restrictions on your cells. Follow the steps to clear them:
  1. Select the cell or cells that you want to remove validation from.
  2. Click Data > Data Tools > Data Validation.
  3. Click the Clear All button.
  4. Click OK.

Then please copy the formula in the cell D5 in the green table. If you cannot, take a screenshot of THE FORMULA IN D5.
Thanks.

Amanda


THANK YOU SO MUCH! It worked.
irene
·
1 year ago
·
1 Likes
·
0 Votes
·
0 Comments
·
View Full Post