OFFSET returns a height and width, so "5,1" in the formula means 5 rows high
and 1 column wide. If you are returning a maximum of 5 cells spanning 5 rows
in 1 column, as in your example, you must have the 5 to tell OFFSET how many
rows (height) and the 1 to specify the width - 1 column to return. If your 5
entries were in one row spanning 5 columns the "5,1" in the formula would be
"1,5" - 1 row high, 5 columns wide. You have to specify the height as 5 in
this case. If you change the height to 1, then the first entry - e.g. Albany
in the case of New York will be returned 5 times - assuming the array
fromula spans 5 rows.

Tyro

"DougL" wrote in message
...
Thank You! This works great and it opens so many more possibilities in
what
I'm creating!!!
Why do I need the "5" in my offset formula as the number of rows that I
want
in my "found response." It also works with a 1 when I was trying to figure
this myself based on your suggested method.

"Tyro" wrote:

I should add that all empty cells in your values must contain ="" if they
are left blank, zeroes will show, not blanks.

Tyro
"Tyro" wrote in message
...
You could for example do this. Assuming you have only 5 possible
values.
Then put in B1 "New York", B2 "Albany", B3 "Manhattan, B4 "Syracuse",
B5
="", B6 ="" Then put in C1 "California", C2 "Sacramento", C3 "San
Diego",
C4 "San Franciso", C5 ="", C6 ="". Then put in D1 "Texas", D2 "Austin",
D3
"Dallas", D4 "San Antonio" D5 "Houston", D6 "Fort Worth". Make sure you
put the equal signs in as in: ="". Assuming you have linked the output
of
your combo box to A1,and you want your resultant values to appear in
H1:H5, select those cells and in H1 enter the array formula
=OFFSET(B1,1,MATCH(A1,B1:D1,0)-1,5,1) and after entering the formula
press
Ctrl+Shift+Enter *not* just Enter. Then you can hide columns B, C and
D.
If you change your possible values to 10, for example, just select
cells
H1:H10, press F2 and change the 5 in the formula to 10 and press
Ctrl+Shift+Enter.

Tyro