my conditions sound like this:
IF D2 is >0 or D2 is <=100, then value is 0-100
IF D2 is >100 or D2 is <=500, then value is 100-500
IF D2 is >500 or D2 is <=1000, then value is 500-1000
IF D2 is >1000 or D2 is <=10000, then value is 1000-10000
IF D2 is >10000 or D2 is <=1000000, then value is 10000-1000000
----------------------------------------------------------------------------------
In the following solution, I have used the VLOOKUP function by setting up a table in cells A1:B7 ... when the LookUp value is entered in cell D2, the value is LookedUp in column A and the result from column B is put in cell E2.
The table in cells A1:B7 can be moved to another location in the same sheet or even to another sheet of the same spreadsheet.
this worked great. thanks... i knew how to do this generally, but didn't know that the formula would result in the identification of ranges for insertion of a value... thought it was 1:1... if this is exactly THAT, then place THIS that's found adjacent to the matched value HERE... the range bit, was the savior and the better answer than from the referring url on the docs help site. +1 Thanks a lot.
ReplyDeleteI'm trying to do a v_Lookup formula using text instead of numerical values. For example, the word "English" corresponds to the word "Anyone". However, whenever I type in the formula like above, I get a #N/A back saying, for example, "Did not find value English". Do I need to edit the formula somehow due to the nature of it being text?
ReplyDeleteHi Emilia:
ReplyDeleteThe formula in my illustration is for non-exact matches ... if you require exact matches, then you need to use the VLOOKUP formula with an additional 4th argument of FALSE (or 0).
I hope this helps -- let me know how it goes.
Cheers!
Yogi