if text in column a is "X" then apply numeric value to column b..help?

Kelly Hogan

Hello! I apologize in advance as I'm sure this is super simple, but for some reason I'm having a hard time and frankly have spent too much time (staring at it, searching formulas, trying+ failing etc ) when I'm sure you smarties can help! 

What I'm trying to do: if text (a specific text/phrase from a dropdown option)  in column A is X then apply numeric value (dollar amount) to column B 

In this sheet, if PROPERTY column is "5222 Kentucky" then apply $200 to column PAYOUT  ..of that same row *or "200" as the column is set up already as currency 

*In addition there are multiple PROPERTY values to select from the drop down (5222 Kentucky, 1105 49th, 921 South ..etc) and each one will have a specific # (dollar amount) to be applied to PAYOUT column.

5222 Kentucky = $200 

1105 49th = $150 

921 South = $220  ..etc. etc. 

Is there a way to set this up so when I type 522..and it auto populates from the drop down PROPERTY column to read "5222 Kentucky" that it then automatically adds "$200" to the PAYOUT column? 

..aka it's annoying to type every payout amount to each property when it's the same every time. 

Hopefully this makes sense and again thank you for any assistance!  

Mike Wilday

Yes, there are actually a couple different approaches but I am going to suggest using a vlookup and keeping a separate sheet with a listing of each of the properties. 

1. In a new sheet List out the address of each property in column one and the rent cost in column two. Name the sheet. 
2. Then, in the cost column begin typing this formula:  =vlookup([Address Column Name]@row, 
3. after entering the comma choose enter a cross sheet reference. You'll see a link to it in the helper box as you enter the last comma. 
4. It will navigate to a pop up of your sheets. Search for the title of the new sheet.
5. Press shift and select both columns and press okay. 
6. Finish the formula with ,2,false) 

The final product should look something like this... =vlookup([Address Column Name]@row, {Cross sheet Reference}, 2, false)

Now when you enter an address it will compare it to your old sheet and return the cost.

https://youtu.be/OCljSnoLIsM?t=27 

Paul Newcome

I was thinking the same thing... A table is definitely the easiest route to go for a situation like this.

Kelly Hogan

Ohh! Ya don't believe I would have figured that one out, but makes sense I'm going to take a further look at all of this today. Thank you soo much!!  

I'm also going to try to see if I can set up different property names with different people names to auto populate costs. Ex 5222 Kentucky /  Ruthy = $200 but 5222 Kentucky / Victoria = $210 ..wish me luck ha! 

Thanks again

Kelly Hogan

Sounds good, thanks Paul!  

Mike Wilday

Good luck. Let us know if you get stuck!  

Paul Newcome

Believe it or not, this actually isn't too complicated. I suggest taking a look at the INDEX and COLLECT functions along with the @cell reference. 

 

As Mike said below... Let us know if you get stuck.