How can I write a formula to add an amount to a VLookup rule based on a number or text string in a different cell?

[FlummoxedByExcel]

Hi there, I had good luck with previous question so I'm asking another one!
I'd like to write a formula that will add 2 to this formula (which returns a number):
=VLOOKUP(E3,Rules_NY!A:T,18,FALSE)
if the value in the same row, Column C in the current sheet is a PO# >=PO64324
I am sure there's a way to do it bc there is a way to do everything in Excel! And members of this board seem to know how.
Thanks for any help!

 

[etaf]

You say greater than PO64324

Then you maybe able to use a RIGHT ()
BUT I'm using MID as i dont know how long the number may be
MID( C3 , 3 , 20 )
I have used 20 as the length , so if the chances are the PO is longer than 20 adjust
As its TEXT will need to change to a number
MID( C3 , 3 , 20 ) *1
Then use an IF

=IF( MID( C3 , 3 , 20 ) *1 >= 64324 , VLOOKUP(E3,Rules_NY!A:T,18,FALSE) + 2, "what to do if PO is less" )

 

[FlummoxedByExcel]

@etaf thank you, however this formula returns an error. Do I need to add a "then" statement? so if the PO is <PO64324, just go with the VLookup return? Or am I missing some other variable or maybe the punctuation isn't correct?
=IF((RIGHT(C3,5)*1)>=64324),VLOOKUP(E3,Rules_NY!A:T,18,FALSE)+2
This formula:
=RIGHT(C3,5)*1)>=64324
Works fine in the cell it's in, as a conditional that highlights it
thanks again!

 

[etaf]

=IF((RIGHT(C3,5)*1)>=64324),VLOOKUP(E3,Rules_NY!A:T,18,FALSE)+2

you have a bracket here
=IF((RIGHT(C3,5)*1)>=64324)
Which completes the IF() hence the ERROR

i have added the true and the false

=IF( MID( C3 , 3 , 20 ) *1 >= 64324 , VLOOKUP(E3,Rules_NY!A:T,18,FALSE) + 2, "what to do if PO is less" )
you DONT need the additional () you have added
The above should WORK as is

The FALSE i added , as i did not know what you want if PO was <
see
"what to do if PO is less"

Did you want just the lookup
=IF( MID( C3 , 3 , 20 ) *1 >= 64324 , VLOOKUP(E3,Rules_NY!A:T,18,FALSE) + 2, VLOOKUP(E3,Rules_NY!A:T,18,FALSE) )

However , if vlookup is returning an ERROR N/A - ie cant find a match
THEN
IFERROR (
IF( MID( C3 , 3 , 20 ) *1 >= 64324 , VLOOKUP(E3,Rules_NY!A:T,18,FALSE) + 2, VLOOKUP(E3,Rules_NY!A:T,18,FALSE) ) , "what to do here IF any ERROR")

 

[FlummoxedByExcel]

@etaf I was already using the "RIGHT" formula successfully and I have a harder time understanding "MID," (I am a "basic Excel" user) so I kept "RIGHT." I actually needed the formula to add 2% if the PO in Col C was over a certain number. It worked! This is what I ended up with:
=IF(RIGHT(C3,5)*1>=64324,VLOOKUP(E3,Rules_NY!A:T,18,FALSE)+0.02,VLOOKUP(E3,Rules_NY!A:T,18,FALSE))
THANK YOU!
I have to say that using this forum is easier than going into the big Excel book and I am learning something too
if you feel like answering another question, can you explain why the *1 multiplier is needed in the 1st statement?
I am using the same statement as a conditional to highlight the cells with the higher PO#s, but it has the extra paren in it: =(RIGHT(C3,5)*1)>=64324
And I thought the close paren between 1)> was necessary to separate the multiplier from the > sign.
Really appreciate the help! thanks again for your time.

 

[Peter_SSs]

This is what I ended up with:
=IF(RIGHT(C3,5)*1>=64324,VLOOKUP(E3,Rules_NY!A:T,18,FALSE)+0.02,VLOOKUP(E3,Rules_NY!A:T,18,FALSE))

A few comments/suggestions in case you are interested.

• If using VLOOKUP, it is best not to include more columns than you are actually needing in the lookup range. In this case, you are retrieving values from column R but are using columns A:T in your range. The reason for my comment is that if any cell in any column (A:T) changes, it will cause your formula to have to recalculate even though the changed value might not be in either column A or column R which are the only two columns relevant to this lookup.
  
  
• Ignoring the point above for a moment, but noting that the particular VLOOKUP in your formula is used in both the TRUE part of the IF statement and the FALSE part, you could condense that formula to this
  

  =VLOOKUP(E3,Rules_NY!A:T,18,FALSE)+0.02*(RIGHT(C3,5)*1>=64324)

  
  
• Returning to the first point above regarding recalculation if anything in any of the 20 columns used in the formula changes, that could be avoided by using INDEX/MATCH instead of VLOOKUP as INDEX/MATCH would only use column A and column R, thus vastly reducing the possibility of a required recalculation. However, since you have Excel 365, I would also bypass INDEX/MATCH and suggest XLOOKUP as it is more efficient. As a result, you might like to try this
  

  =XLOOKUP(E3,Rules_NY!A:A,Rules_NY!R:R)+0.02*(RIGHT(C3,5)-64324>=0)

  Again, this has the advantage of only triggering a recalculation if something in column A or column R of Rules_NY changes (or E3 or C3 of the formula sheet of course)

 

[FlummoxedByExcel]

Hello @Peter_SSs thanks for your reply above. It is beyond my Excel knowledge — I have to look up index/match and Xlookup functions. I think I understand the logic of the Vlookup in the 2nd bullet point above but I don't understand how the asterisk * is being used — I only know to use it to mean "multiply" but it's obviously not doing that in the formulas above. I was a typesetter back before Macs and it involved a great deal of coding. We had a standard of "elegant" code which meant using the fewest characters and the most direct route to define a block of type. I see there is definitely "elegant" Excel code as well, but for the moment I have to satisfy myself with "working well enough." ! thank you again.

 

[Peter_SSs]

I only know to use it to mean "multiply" but it's obviously not doing that in the formulas above.

Yes it is meaning multiply there too.

(RIGHT(C3,5)-64324>=0) will return either True (if the PO number is >= 64324) or False if not.
In Excel True/False values are coerced to 1/0 numbers if any mathematical operation is attempted with them.
So, the asterisk ..
- multiplies 0.2 by 1 if the PO number is >= 64324 and therefore adds 0.2 to the looked-up value in that case.
- multiplies 0.2 by 0 if the PO number is < 64324 and therefore adds nothing to the looked-up value in that case.