combined VLookup and If formula?

[dbwiz]

I have a VLOOKUP in AO which asks for data in AE and then looks in table SCAL for the data and returns 3 (3rd column). I need to add an If formula to this so that if AN is <1, make results in AO = *-1 (in other words, make it a negative).

Example: AE3 holds text data of 99202. AN3 is -75.58. VLOOKUP for 99202 would return 79.73, but since AN is -75.58 I need it to return -79.73. How can I do this?

 

[Aladin Akyurek]

I have a VLOOKUP in AO which asks for data in AE and then looks in table SCAL for the data and returns 3 (3rd column). I need to add an If formula to this so that if AN is <1, make results in AO = *-1 (in other words, make it a negative).

Example: AE3 holds text data of 99202. AN3 is -75.58. VLOOKUP for 99202 would return 79.73, but since AN is -75.58 I need it to return -79.73. How can I do this?

Maybe:

VLOOKUP(...)*SIGN(AN3)

 

[Marcelo Branco]

Maybe multiplying your original formula by (1-2*(AN3<1)), ie, something like:

=VLOOKUP(...)*(1-2*(AN3<1))