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..

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?