Nesting muddle

[BladeFisher]

I am endeavouring to convert columns and rows for use in a 'movement' scenario, where I need Excel to tell me how far apart in rows and columns two cells are in different worksheets. To do this I have a lookup table that converts columns to numbers and vice versa. However the relative position of rows and columns is important as in essence there are six broad senarios.

The position of cell B in rows is greater than A
The position of cell A in rows is greater than B
The position of cell A in rows is equal to B
The position of cell B in columns is greater than A
The position of cell A in columns is greater than B
The position of cell A in columns is equal to B

I have the following furmala:

=IF(VLOOKUP(E8,ColNumCol!$E$1:$F$256,2,FALSE)>(VLOOKUP(E7,ColNumCol!$E1:$F256,2,FALSE)),"(VLOOKUP(E7,ColNumCol!$E$1:$F$256,2,FALSE))-(VLOOKUP(E8,ColNumCol!$E1:$F256,2,FALSE)),((VLOOKUP(E8,ColNumCol!$E$1:$F$256,2,FALSE))-(VLOOKUP(E7,ColNumCol!$E1:$F256,2,FALSE))).

Now I know it is incorrect and I haven't yet decided what I need to achieve if the values for columns are equal but can someone advise what the correct syntax is for getting either a positive or negative number with this formula under conditions 1 and 2 (rows are self explanatory).

Viz if Column M=13 > Column D= 4 perform one calculation
(take 4 - 13 = -9) if it is the converse then (13 - 4 = 9). The two values or bits of data are in cells E7 and E8, of course these can vary in their location.

 

[pushkardey]

Ok frankly I havent gone through the whole function of yours, however I can tell you that based upon your requirement you will require MATCH function rather than vlookup.
This is because you need numerical reference of a value in a particular array. It has nearly the same syntax as vlookup. try it !!

 

[BladeFisher]

Thanks but I persisted with VLOOKUP and got it to work. Just a parenthesis thing I think.

What helped was using simple terms like greater or lower in the alternative and do nothing scenarios then putting formulas into them one by one to get it to work.