combined VLookup and If formula?

dbwiz

Active Member
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?

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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)

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

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

Replies
1
Views
371
Replies
8
Views
329
Replies
15
Views
321
Replies
1
Views
161
Replies
2
Views
283

1,203,189
Messages
6,054,002
Members
444,696
Latest member
VASUCH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back