Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
anyway around this, easily?
i got a scenario wherein i have to use a short hand way of lookup between
different workbooks.. i simplified the lookup by having those reference values and lookup values become constant and inside the active worksheet. something like this A1 contains {1,2,3} A2 contains {-3,-2,-1} then from the lookup's fx form box lookup value : 3 lookup reference : ""&A1 : DISPLAYED AS ="{1,2,3}" lookup value : ""&A2 : DISPLAYED AS ="{-3,-2,-1}" the result is #VALUE! because of the Double Quotes.... yet, assuming if I type from B1=1,B2=2,B3=3 and C1=-3,C2=-2,C3=-1 and i do the lookup formula =lookup(3,B1:B3,C1:C3) lookup value : 3 lookup reference : B1:B3 : DISPLAYED AS ={1,2,3} lookup value : C1:C3 : DISPLAYED AS ={-3,-2,-1} the result is -1 which is correct.. how can i eliminate the double quote's that will be read by the formbox...considering that i am in dire need to use the lookup(3,""&A1,""&A2)... i hope it will make sense to look for a more faster search between different workbooks by a shorter formula. maybe some kinda wildcard can do this workaround -REMOVE QUOTES! regards and more power, dribler2 -- ***** birds of the same feather flock together.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
anyway around this, easily?
dribler2 wrote...
.... A1 contains {1,2,3} A2 contains {-3,-2,-1} If A1 and A2 have these literal contents, then both are text. That is, formulas in other cells could have references to A1 replaced by "{1,2,3}". That's *NOT* an array, nor is it easily converted into an array. then from the lookup's fx form box lookup value : 3 lookup reference : ""&A1 : DISPLAYED AS ="{1,2,3}" lookup value : ""&A2 : DISPLAYED AS ="{-3,-2,-1}" This is a bug in the function wizard. The dialog may display them as "{1,2,3}" and "{-3,-2,-1}", but there are no trailing double quotes. the result is #VALUE! because of the Double Quotes.... It's #VALUE! in part because you're comparing a number (first argument 3) to a degenerate single item array containing only the text string "{1,2,3}. Excel's lookup functions are finicky. They INSIST that if your 1st argument is a number, then the lookup column/row in which that value is sought must contain numbers. yet, assuming if I type from B1=1,B2=2,B3=3 and C1=-3,C2=-2,C3=-1 and i do the lookup formula =lookup(3,B1:B3,C1:C3) lookup value : 3 lookup reference : B1:B3 : DISPLAYED AS ={1,2,3} lookup value : C1:C3 : DISPLAYED AS ={-3,-2,-1} the result is -1 which is correct.. This time your 2nd and 3rd arguments are ranges, so they evaluate to arrays containing numbers. Your B1:B3 range evaluates to {1,2,3}, NOT "{1,2,3}". This is similar to the fact that X77 containing 5 is NOT equal to Y99 containing ="5". Excel considers ALL numbers and ALL text to be different in ALL comparisons. how can i eliminate the double quote's that will be read by the formbox...considering that i am in dire need to use the lookup(3,""&A1,""&A2)... This is a seriously flawed formula. In plain text, what do you believe it accomplishes? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I easily link a block of cells in one spreadsheet to anothe | Excel Discussion (Misc queries) | |||
Lock easily breaks. | Excel Discussion (Misc queries) | |||
how to copy the same cell across different work books into another workbook easily? | Excel Discussion (Misc queries) | |||
How to easily collapse columns? | Excel Discussion (Misc queries) | |||
How do I easily draw in Excel the Upper and Lower limits of a con. | Charts and Charting in Excel |