CHOOSE & VLOOKUP; Weird Behavior
 

I entered the following two functions (below) into my spreadsheeet and get
the results I expect, except if I enter "SELLER" or "TAX INSURANCE" into
B276. Does anyone have any thoughts as to why this could be?

=CHOOSE(MATCH(B276,{"BUYER";"SELLER";"TAX INSURANCE";"CONTINGENT
INSURANCE";"DEED REPLACEMENT";"CONTINGENT DEED REPLACEMENT"},1),"FIFTY-FIFTY")

=VLOOKUP(B276,A312:A319,1)


Cordially,
Ryan---
--
RyGuy

CHOOSE & VLOOKUP; Weird Behavior
 

I wonder if your array needs to be listed in alphabetical order.

"ryguy7272" wrote:

I entered the following two functions (below) into my spreadsheeet and get
the results I expect, except if I enter "SELLER" or "TAX INSURANCE" into
B276. Does anyone have any thoughts as to why this could be?

=CHOOSE(MATCH(B276,{"BUYER";"SELLER";"TAX INSURANCE";"CONTINGENT
INSURANCE";"DEED REPLACEMENT";"CONTINGENT DEED REPLACEMENT"},1),"FIFTY-FIFTY")

=VLOOKUP(B276,A312:A319,1)


Cordially,
Ryan---
--
RyGuy

CHOOSE & VLOOKUP; Weird Behavior
 

ry

The MATCH function of B276 in your listed array returns 1 for
everything except for SELLER and TAX INSURANCE, so the CHOOSE function
works, and selects "FIFTY-FIFTY" since it is the only option. For
SELLER and TAX INSURANCE, the MATCH function evaluates to 2 and 3
respectively, causing an error since you only have one items in your
list to choose from. Putting the array in alphabetic order may fix
the VLOOKUP function, but, CHOOSE/MATCH combo function will give an
error for everything other than BUYER, since everything else will
cause the MATCH function to return a number greater than 1 and
therefore give an #VALUE since you only list one option from which to
choose. It is easy to see why your formula does not work, but, it is
not clear enough what you wanted it to do to help you fix it.

Good luck.

Ken
Norfolk, Va

On Jul 27, 2:30 pm, Barb Reinhardt
wrote:
I wonder if your array needs to be listed in alphabetical order.



"ryguy7272" wrote:
I entered the following two functions (below) into my spreadsheeet and get
the results I expect, except if I enter "SELLER" or "TAX INSURANCE" into
B276. Does anyone have any thoughts as to why this could be?

=CHOOSE(MATCH(B276,{"BUYER";"SELLER";"TAX INSURANCE";"CONTINGENT
INSURANCE";"DEED REPLACEMENT";"CONTINGENT DEED REPLACEMENT"},1),"FIFTY-FIFTY")

=VLOOKUP(B276,A312:A319,1)

Cordially,
Ryan---
--
RyGuy- Hide quoted text -

- Show quoted text -

CHOOSE & VLOOKUP; Weird Behavior
 

I eliminated one of the items (which turned out to be superfluous) and went
with a five-condition-if-function. It is clunky, but works. Thanks for the
assistance Barb and Ken. Ken, I will look at your comments more closely
tonight.

Regards,
Ryan---


--
RyGuy


"Ken" wrote:

ry

The MATCH function of B276 in your listed array returns 1 for
everything except for SELLER and TAX INSURANCE, so the CHOOSE function
works, and selects "FIFTY-FIFTY" since it is the only option. For
SELLER and TAX INSURANCE, the MATCH function evaluates to 2 and 3
respectively, causing an error since you only have one items in your
list to choose from. Putting the array in alphabetic order may fix
the VLOOKUP function, but, CHOOSE/MATCH combo function will give an
error for everything other than BUYER, since everything else will
cause the MATCH function to return a number greater than 1 and
therefore give an #VALUE since you only list one option from which to
choose. It is easy to see why your formula does not work, but, it is
not clear enough what you wanted it to do to help you fix it.

Good luck.

Ken
Norfolk, Va

On Jul 27, 2:30 pm, Barb Reinhardt
wrote:
I wonder if your array needs to be listed in alphabetical order.



"ryguy7272" wrote:
I entered the following two functions (below) into my spreadsheeet and get
the results I expect, except if I enter "SELLER" or "TAX INSURANCE" into
B276. Does anyone have any thoughts as to why this could be?

=CHOOSE(MATCH(B276,{"BUYER";"SELLER";"TAX INSURANCE";"CONTINGENT
INSURANCE";"DEED REPLACEMENT";"CONTINGENT DEED REPLACEMENT"},1),"FIFTY-FIFTY")

=VLOOKUP(B276,A312:A319,1)

Cordially,
Ryan---
--
RyGuy- Hide quoted text -

- Show quoted text -