ExcelBanter - Extracting Data from another worksheet based on user input

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

"