Dear Sir/Mdm,

Can anyone advise me how to perform the following in Excel?

On worksheet B, I have a list of data in column A.
Eg, A1=Holland, A2=England, A3=New Zealand, A4=Australia, A5=Asia.

What I want to achieve is whenever I type any key in worksheet A on a Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B and
display the value in Cell B2 of worksheet A.

Please advise and many thanks.

Alvyn

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on a Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a value in
the range A1 to A5 in Worksheet B, it will then display that value in Cell B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A with the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box? and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on a Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift + Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a value
in
the range A1 to A5 in Worksheet B, it will then display that value in Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift + Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a value
in
the range A1 to A5 in Worksheet B, it will then display that value in Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Did you enter the formula by holding down the Ctrl & Shift keys while you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift +
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a
value
in
the range A1 to A5 in Worksheet B, it will then display that value in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the
range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on
a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Dear Sandy

OK. Now it works. Thanks for your precious advice.

:)

Alvyn

"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys while you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift +
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a
value
in
the range A1 to A5 in Worksheet B, it will then display that value in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the
range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on
a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Dear Sandy

In the INDEX(array, row_num, col_num) formula, can I replace the row_num &
col_num with a variable?

Thanks

Alvyn



"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys while you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift +
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a
value
in
the range A1 to A5 in Worksheet B, it will then display that value in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the
range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on
a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Dear Sandy,

In the INDEX(array, row_num, col_num) formula, can I replace the row_num
and/or col_num with a variable?

Thanks

Alvyn

"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys while you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift +
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a
value
in
the range A1 to A5 in Worksheet B, it will then display that value in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the
range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on
a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

I don't really understand what you mean by *variable*

Actually I am not using the col_num argument, (the
MATCH(LEFT(B2,3),LEFT(Sheet2!A1:A5,3),0) returns *one* number, is that what
you meant by variable?), so the col_num argumant defaults to 1.

If you meant a cell relerence then yes, you can write it as:

=INDEX(Sheet2!A1:A5,B1,C1)

What is it that you are trying to do?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy,

In the INDEX(array, row_num, col_num) formula, can I replace the row_num
and/or col_num with a variable?

Thanks

Alvyn

"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys while you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift +
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a
value
in
the range A1 to A5 in Worksheet B, it will then display that value
in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the
range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo
box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A
on
a
Cell
B2, it is able to lookup the information from A1 to A5 in
worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Dear Sandy

My "variable" means, a temporary space in memory.

Eg, ROW_ENTRY & COL_ENTRY are variables and its value is determine by the
input of certain value in a specific cell, say B2 & C2.

Therefore, if I enter "10" in cell B2 and "20" in cell C2, ROW_ENTRY will
hold the value 10 and COL_ENTRY will hold the value 20 respectively.

So, if I use INDEX(array, row_num, col_num) formula, it will achieve
something like:
INDEX(array, 10, 20)

And if I enter "A" in cell B2 and "B" in cell C2, ROW_ENTRY will hold the
value A and COL_ENTRY will hold the value B respectively.

And the result will look something like :
INDEX(array, A, B)

Is this possible in any of the EXCEL's functions, formula etc?

Thanks

Alvyn


"Sandy Mann" wrote:

I don't really understand what you mean by *variable*

Actually I am not using the col_num argument, (the
MATCH(LEFT(B2,3),LEFT(Sheet2!A1:A5,3),0) returns *one* number, is that what
you meant by variable?), so the col_num argumant defaults to 1.

If you meant a cell relerence then yes, you can write it as:

=INDEX(Sheet2!A1:A5,B1,C1)

What is it that you are trying to do?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy,

In the INDEX(array, row_num, col_num) formula, can I replace the row_num
and/or col_num with a variable?

Thanks

Alvyn

"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys while you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift +
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a
value
in
the range A1 to A5 in Worksheet B, it will then display that value
in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the
range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo
box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A
on
a
Cell
B2, it is able to lookup the information from A1 to A5 in
worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

My "variable" means, a temporary space in memory.

I've been calling that a *senior moment* <VBG

You can Define Names in Excel (Insert Name Define) - say you use your A
& B - and give the names a value then you those names in the function like:

=INDEX(A1:B10,A,B)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

My "variable" means, a temporary space in memory.

Eg, ROW_ENTRY & COL_ENTRY are variables and its value is determine by the
input of certain value in a specific cell, say B2 & C2.

Therefore, if I enter "10" in cell B2 and "20" in cell C2, ROW_ENTRY will
hold the value 10 and COL_ENTRY will hold the value 20 respectively.

So, if I use INDEX(array, row_num, col_num) formula, it will achieve
something like:
INDEX(array, 10, 20)

And if I enter "A" in cell B2 and "B" in cell C2, ROW_ENTRY will hold the
value A and COL_ENTRY will hold the value B respectively.

And the result will look something like :
INDEX(array, A, B)

Is this possible in any of the EXCEL's functions, formula etc?

Thanks

Alvyn


"Sandy Mann" wrote:

I don't really understand what you mean by *variable*

Actually I am not using the col_num argument, (the
MATCH(LEFT(B2,3),LEFT(Sheet2!A1:A5,3),0) returns *one* number, is that
what
you meant by variable?), so the col_num argumant defaults to 1.

If you meant a cell relerence then yes, you can write it as:

=INDEX(Sheet2!A1:A5,B1,C1)

What is it that you are trying to do?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy,

In the INDEX(array, row_num, col_num) formula, can I replace the
row_num
and/or col_num with a variable?

Thanks

Alvyn

"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys while
you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet
A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you
would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift
+
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches
a
value
in
the range A1 to A5 in Worksheet B, it will then display that
value
in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search
the
range
A1 to A5 in worksheet B and return a value on Cell B2 in
Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using
combo
box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet
A
on
a
Cell
B2, it is able to lookup the information from A1 to A5 in
worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

I forgot to point out that you can define A & B but not C because C is a
reserved name in XL ( for Column)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
My "variable" means, a temporary space in memory.

I've been calling that a *senior moment* <VBG

You can Define Names in Excel (Insert Name Define) - say you use your
A & B - and give the names a value then you those names in the function
like:

=INDEX(A1:B10,A,B)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

My "variable" means, a temporary space in memory.

Eg, ROW_ENTRY & COL_ENTRY are variables and its value is determine by the
input of certain value in a specific cell, say B2 & C2.

Therefore, if I enter "10" in cell B2 and "20" in cell C2, ROW_ENTRY will
hold the value 10 and COL_ENTRY will hold the value 20 respectively.

So, if I use INDEX(array, row_num, col_num) formula, it will achieve
something like:
INDEX(array, 10, 20)

And if I enter "A" in cell B2 and "B" in cell C2, ROW_ENTRY will hold the
value A and COL_ENTRY will hold the value B respectively.

And the result will look something like :
INDEX(array, A, B)

Is this possible in any of the EXCEL's functions, formula etc?

Thanks

Alvyn


"Sandy Mann" wrote:

I don't really understand what you mean by *variable*

Actually I am not using the col_num argument, (the
MATCH(LEFT(B2,3),LEFT(Sheet2!A1:A5,3),0) returns *one* number, is that
what
you meant by variable?), so the col_num argumant defaults to 1.

If you meant a cell relerence then yes, you can write it as:

=INDEX(Sheet2!A1:A5,B1,C1)

What is it that you are trying to do?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy,

In the INDEX(array, row_num, col_num) formula, can I replace the
row_num
and/or col_num with a variable?

Thanks

Alvyn

"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys while
you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in
Worksheet A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you
would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl +
Shift +
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches
a
value
in
the range A1 to A5 in Worksheet B, it will then display that
value
in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search
the
range
A1 to A5 in worksheet B and return a value on Cell B2 in
Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using
combo
box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in
worksheet A
on
a
Cell
B2, it is able to lookup the information from A1 to A5 in
worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Dear Sandy

Thank you very much. You advice has saved me many nights cracking my head
and web searches and I really appreciate it. :)

Once again thank you.

Sincerely,

Alvyn


"Sandy Mann" wrote:

My "variable" means, a temporary space in memory.

I've been calling that a *senior moment* <VBG

You can Define Names in Excel (Insert Name Define) - say you use your A
& B - and give the names a value then you those names in the function like:

=INDEX(A1:B10,A,B)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

My "variable" means, a temporary space in memory.

Eg, ROW_ENTRY & COL_ENTRY are variables and its value is determine by the
input of certain value in a specific cell, say B2 & C2.

Therefore, if I enter "10" in cell B2 and "20" in cell C2, ROW_ENTRY will
hold the value 10 and COL_ENTRY will hold the value 20 respectively.

So, if I use INDEX(array, row_num, col_num) formula, it will achieve
something like:
INDEX(array, 10, 20)

And if I enter "A" in cell B2 and "B" in cell C2, ROW_ENTRY will hold the
value A and COL_ENTRY will hold the value B respectively.

And the result will look something like :
INDEX(array, A, B)

Is this possible in any of the EXCEL's functions, formula etc?

Thanks

Alvyn


"Sandy Mann" wrote:

I don't really understand what you mean by *variable*

Actually I am not using the col_num argument, (the
MATCH(LEFT(B2,3),LEFT(Sheet2!A1:A5,3),0) returns *one* number, is that
what
you meant by variable?), so the col_num argumant defaults to 1.

If you meant a cell relerence then yes, you can write it as:

=INDEX(Sheet2!A1:A5,B1,C1)

What is it that you are trying to do?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy,

In the INDEX(array, row_num, col_num) formula, can I replace the
row_num
and/or col_num with a variable?

Thanks

Alvyn

"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys while
you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet
A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you
would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift
+
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches
a
value
in
the range A1 to A5 in Worksheet B, it will then display that
value
in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search
the
range
A1 to A5 in worksheet B and return a value on Cell B2 in
Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using
combo
box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet
A
on
a
Cell
B2, it is able to lookup the information from A1 to A5 in
worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"

Your are very welcome. Thanks for posting back

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thank you very much. You advice has saved me many nights cracking my head
and web searches and I really appreciate it. :)

Once again thank you.

Sincerely,

Alvyn


"Sandy Mann" wrote:

My "variable" means, a temporary space in memory.

I've been calling that a *senior moment* <VBG

You can Define Names in Excel (Insert Name Define) - say you use your
A
& B - and give the names a value then you those names in the function
like:

=INDEX(A1:B10,A,B)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

My "variable" means, a temporary space in memory.

Eg, ROW_ENTRY & COL_ENTRY are variables and its value is determine by
the
input of certain value in a specific cell, say B2 & C2.

Therefore, if I enter "10" in cell B2 and "20" in cell C2, ROW_ENTRY
will
hold the value 10 and COL_ENTRY will hold the value 20 respectively.

So, if I use INDEX(array, row_num, col_num) formula, it will achieve
something like:
INDEX(array, 10, 20)

And if I enter "A" in cell B2 and "B" in cell C2, ROW_ENTRY will hold
the
value A and COL_ENTRY will hold the value B respectively.

And the result will look something like :
INDEX(array, A, B)

Is this possible in any of the EXCEL's functions, formula etc?

Thanks

Alvyn


"Sandy Mann" wrote:

I don't really understand what you mean by *variable*

Actually I am not using the col_num argument, (the
MATCH(LEFT(B2,3),LEFT(Sheet2!A1:A5,3),0) returns *one* number, is that
what
you meant by variable?), so the col_num argumant defaults to 1.

If you meant a cell relerence then yes, you can write it as:

=INDEX(Sheet2!A1:A5,B1,C1)

What is it that you are trying to do?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy,

In the INDEX(array, row_num, col_num) formula, can I replace the
row_num
and/or col_num with a variable?

Thanks

Alvyn

"Sandy Mann" wrote:

Did you enter the formula by holding down the Ctrl & Shift keys
while
you
pressed the Enter key?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in
Worksheet
A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you
would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl +
Shift
+
Enter
not just Enter on its own.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it
matches
a
value
in
the range A1 to A5 in Worksheet B, it will then display that
value
in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will
search
the
range
A1 to A5 in worksheet B and return a value on Cell B2 in
Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using
combo
box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in
worksheet
A
on
a
Cell
B2, it is able to lookup the information from A1 to A5 in
worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"