Solved If a range contains specific text, return adjacent cell valu

March 21, 2017 at 07:50:20
Specs: Windows 64
If a column of text has a specific value "New Deal" how to I get it to return the cell next to it that contains the dollar amount? I am wanting Excel to quickly sort and enter the data from the left three columns into the middle section to separate everything out. All of the equals signs below are what I need a formula for. It's not keeping my formatting, but each entity will have its own column and I'm needing excel to sort through this list and put in the corresponding values into the correct columns. So for example I need the left 3 columns searched for "New Deal" if the range contains it I need it to return the amount beside it to the "New Deal" Column

Date Entity Amount New Deal Gale Valley Freeway Dr
1/4/2012 New Deal $(3,500.00) =$(3500)
1/4/2012 Gale Valley $3,583.34 =$3583.34
1/6/2012 Freeway Dr $2,074.50 =$2074.50
1/6/2012 New Deal $276.66 = $276.66

message edited by mel1012003


See More: If a range contains specific text, return adjacent cell valu

Report •

#1
March 21, 2017 at 08:58:31
I'm sure that we can offer a solution, but it's a little hard to work with your data based on the way it is laid out.

Please click on the link at the bottom of this post and follow the instructions on how to post example data in this forum. Once the data as been formatted so that we can determine what data is in which column, it will be much easier fro use to supply a solution.

Please be sure to include Column Letters and Row numbers when you post the data.

Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 21, 2017 at 11:31:32
✔ Best Answer
Assuming you have this in B2, the following formula should work:

       A              B
1
2            New Deal $(3,500.00)

Put this in C2 and drag it down:

=IF(LEFT(B2,8)="New Deal",RIGHT(B2,LEN(B2)-9)*1,"")

What this formula will do is look at the left 8 characters in B2 and if they "equal" New Deal, it will extract the characters on the right side of cell starting after position 9. That should the position of the first character after the space after New Deal.

Once it has that result, it multiplies it by 1 to turn the text string into a number, which is what I assume you want.

In the example above:

LEFT(B2,8)="New Deal" ----> TRUE

RIGHT(B2,LEN(B2)-9) ----> $(3,500.00) as a text string

RIGHT(B2,LEN(B2)-9)*1 ----> -3500

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
April 22, 2017 at 21:31:13
how can I check a range and when the text exists in the range, have the result be the value in the adjacent cell of where that text is in the range.

        A           B
1	jack          1
2	donna         7
3	lizzy         3
4	joe           5
5	nancy         6


the first range is A1:A5 and the range is named "NAME".
the second range is B1:B5 and named "SCORE"
I want to check if the text in G10 is range NAME and if it is, return the SCORE. So if "donna" is in range NAME, I want 7 returned. If the text in G10 is not in the range NAME, return "doh".
I was trying this but i cannot seem to get it.

=IF(COUNTIF(NAME=G10,SCORE,"doh")

I want to enter names in another tab and not have to enter the values in that new tab every time I enter the names. So I will have say 5 additional sheets over time, each with the students names but not all of the students in each tab every time. I can do a dropdown in the Name column of the additional tabs and depending on the name I select, I would like the "score" column in the new tab to auto-populate with the score for the each name from the range.

Thank you!

johnson


...got it. VLOOKUP! Thanks anyway.

Consolidate the range to a single range of A1:B5 and name it NAMES_SCORES.

=VLOOKUP(G10,NAMES_SCORES, 2, FALSE)

message edited by johnson99


Report •
Related Solutions


Ask Question