how to combine Logical if and Vlook up
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 76 IT SHOULD BE -10.525, AND THAT OTHER NUMBER IT SHOULD BE AS PER SHEET-2 |
how to combine Logical if and Vlook up
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("T bl_"&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. -- John C "vmohan1978" wrote: 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 76 IT SHOULD BE -10.525, AND THAT OTHER NUMBER IT SHOULD BE AS PER SHEET-2 |
how to combine Logical if and Vlook up
|
All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com