how to combine Logical if and Vlook up

[vmohan1978]

PAGE-1

A B C D( FORMULA REQUIRED
Sl.No Location Number Cut off Level
1 S 3
2 N 45
3 E 9
4 W 55
5 S 70
6 N 10
7 S 15
8 S 50



Page-2
A B C
Location Number Cut off Level
S 1 -1
S 2 -1.150
S 3 -1.312
S 4 -1.425
S 5 -1.44
S 6 -1.52
S 7 -1.59
S 8 -1.68
S 9 -3
S >10<=74 -4.90
S >75 -10.525
E 1 -2.52
E 2 -2.78
E 3 -2.98
E 4 -3.45
E 5 -3.65
E 6 -3.85
E 7 -4.01
N 1-50 -7.90
W From 1-50 -4.90


i NEED A FORMULA IN PAGE1 COLUMN D BASED ON THE INPUT IN PAGE 2 FOR NORTH
DIRECTION WHAT EVER THE NUMBER UPTO 50 THE VALUE SHOULD BE -7.90 AND THAT OF
DIRECTION"W" IT SHOULD BE 4.90 AND FOR THE "S" >10<=75 IT SHOULD BE -4.90 AND

 

[John C]

I had to make some changes, and some assumptions.
I am starting my input tab entry location in B2, and number in C2.
I am making the following assumptions:
All values entered in column C are a whole number greater than 0.
Values for S can be from 1 to whatever (I had an arbitrary upper limit of
100).
Values for E can be from 1 to 7 only.
Values for N and W can be from 1 to 50 only.
2 additional assumptions for S, values of 10 and 75 ARE valid for S, and
your original table should have been

=10<=74
=75

If 10 and 75 are not valid entries for S, there is a way around that, see
end of this post.

On Data tab, I entered:
Cells A1:A12 - I entered an S
Cells A13:A20 - I entered an E
Cells A21:A22 - I entered an N
Cells A23:A24 - I entered a W
These entries are strictly informational.
I then entered the following values from cells B1 through B24:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 75, 100, 1, 2, 3, 4, 5, 6, 7, 8, 1, 51, 1, 51
I then entered the following values from cells C1 through C24:
-1, -1.15, -1.312, -1.425, -1.44, -1.52, -1.59, -1.68, -3, -4.9, -10.525,
Invalid Data, -2.52, -2.78, -2.98, -3.45, -3.65, -3.85, -4.01, Invalid Data,
-7.9, Invalid Data, -4.9, Invalid Data
Highlight section B1:C12, Insert|Name|Define, I named selection Tbl_S
Highlight section B13:C20, Insert|Name|Define, I named selection Tbl_E
Highlight section B21:C22, Insert|Name|Define, I named selection Tbl_N
Highlight section B23:C24, Insert|Name|Define, I named selection Tbl_W

On your input tab, your letter entry column in section B, I used Data
Validation, so highlight section of tab where this entry will be, go to
Data|Validation, Allow: List, Source: S, N, E, W
I also used validation on column C entries, again operating under the
assumption that this is a whole number always greater than 0.
Go to Data|Validation
Allow: Whole Number, Data: Greater than or equal to, Minimum: 1

And finally, the formula in cell D2
=IF(OR(B2="",C2="",C2<1),"",VLOOKUP(C2,INDIRECT("Tbl_"&B2),2,TRUE))
and copy down as needed.

Some of my assumptions may be incorrect, for example, if S x 10 is an
invalid data, type Invalid Data into cell C10 on your data tab. You would
then need to insert a row and add 11 in column B, and -4.9 in column C.
Similar to what is needed if S x 75 is not a valid entry.

Hope this helps.

 

[vmohan1978]

Dear John,
It worked 100% correctly as per my requirement.
I am very thankful to you and learned new things so i can apply to other
problems also.
If you don;t mind can you send some excel files where i can learn how to
write the formula to my email id (e-mail address removed).
Once again Thanking you.

Regards,

Mohan