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
>
> sandymann2 RemoveThis @mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Alvyn" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> news:B135C858-6FA7-482A-B13A-D1CCF771CD80@microsoft.com...
> > 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
> >>
> >> sandymann2 RemoveThis @mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "Alvyn" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> >> news:4B32EAA6-842F-4D79-9675-3378E3EDA8D6@microsoft.com...
> >> > 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!A1: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
> >> >>
> >> >> sandymann2 RemoveThis @mailinator.com
> >> >> Replace @mailinator.com with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "Alvyn" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> >> >> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
> >> >> > 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" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> >> >> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
> >> >> >> >
> >> >> >> > 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
> >> >> >>
> >> >> >> sandymann2 RemoveThis @mailinator.com
> >> >> >> 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

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


"Alvyn" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
news:6D14AFB8-3FBA-40F6-8437-6AC014A0DF52@microsoft.com...
> 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
>>
>> sandymann2 DeleteThis @mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Alvyn" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
>> news:B135C858-6FA7-482A-B13A-D1CCF771CD80@microsoft.com...
>> > 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
>> >>
>> >> sandymann2 DeleteThis @mailinator.com
>> >> Replace @mailinator.com with @tiscali.co.uk
>> >>
>> >>
>> >> "Alvyn" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
>> >> news:4B32EAA6-842F-4D79-9675-3378E3EDA8D6@microsoft.com...
>> >> > 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!A1: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
>> >> >>
>> >> >> sandymann2 DeleteThis @mailinator.com
>> >> >> Replace @mailinator.com with @tiscali.co.uk
>> >> >>
>> >> >>
>> >> >> "Alvyn" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
>> >> >> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
>> >> >> > 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" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
>> >> >> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
>> >> >> >> >
>> >> >> >> > 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
>> >> >> >>
>> >> >> >> sandymann2 DeleteThis @mailinator.com
>> >> >> >> 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
>
> sandymann2 RemoveThis @mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Alvyn" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> news:6D14AFB8-3FBA-40F6-8437-6AC014A0DF52@microsoft.com...
> > 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
> >>
> >> sandymann2 RemoveThis @mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "Alvyn" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> >> news:B135C858-6FA7-482A-B13A-D1CCF771CD80@microsoft.com...
> >> > 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
> >> >>
> >> >> sandymann2 RemoveThis @mailinator.com
> >> >> Replace @mailinator.com with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "Alvyn" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> >> >> news:4B32EAA6-842F-4D79-9675-3378E3EDA8D6@microsoft.com...
> >> >> > 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!A1: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
> >> >> >>
> >> >> >> sandymann2 RemoveThis @mailinator.com
> >> >> >> Replace @mailinator.com with @tiscali.co.uk
> >> >> >>
> >> >> >>
> >> >> >> "Alvyn" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> >> >> >> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
> >> >> >> > 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" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> >> >> >> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
> >> >> >> >> >
> >> >> >> >> > 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
> >> >> >> >>
> >> >> >> >> sandymann2 RemoveThis @mailinator.com
> >> >> >> >> 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

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


"Sandy Mann" <sandymann2.RemoveThis@mailinator.com> wrote in message
news:eGNz%23899IHA.1448@TK2MSFTNGP02.phx.gbl...
>> 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
>
> sandymann2.RemoveThis@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Alvyn" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
> news:6D14AFB8-3FBA-40F6-8437-6AC014A0DF52@microsoft.com...
>> 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
>>>
>>> sandymann2.RemoveThis@mailinator.com
>>> Replace @mailinator.com with @tiscali.co.uk
>>>
>>>
>>> "Alvyn" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
>>> news:B135C858-6FA7-482A-B13A-D1CCF771CD80@microsoft.com...
>>> > 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
>>> >>
>>> >> sandymann2.RemoveThis@mailinator.com
>>> >> Replace @mailinator.com with @tiscali.co.uk
>>> >>
>>> >>
>>> >> "Alvyn" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
>>> >> news:4B32EAA6-842F-4D79-9675-3378E3EDA8D6@microsoft.com...
>>> >> > 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!A1: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
>>> >> >>
>>> >> >> sandymann2.RemoveThis@mailinator.com
>>> >> >> Replace @mailinator.com with @tiscali.co.uk
>>> >> >>
>>> >> >>
>>> >> >> "Alvyn" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
>>> >> >> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
>>> >> >> > 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" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
>>> >> >> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
>>> >> >> >> >
>>> >> >> >> > 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
>>> >> >> >>
>>> >> >> >> sandymann2.RemoveThis@mailinator.com
>>> >> >> >> 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

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


"Alvyn" <Alvyn.DeleteThis@discussions.microsoft.com> wrote in message
news:A5978705-C655-4FFF-8631-29FE8A587E18@microsoft.com...
> 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
>>
>> sandymann2.DeleteThis@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Alvyn" <Alvyn.DeleteThis@discussions.microsoft.com> wrote in message
>> news:6D14AFB8-3FBA-40F6-8437-6AC014A0DF52@microsoft.com...
>> > 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
>> >>
>> >> sandymann2.DeleteThis@mailinator.com
>> >> Replace @mailinator.com with @tiscali.co.uk
>> >>
>> >>
>> >> "Alvyn" <Alvyn.DeleteThis@discussions.microsoft.com> wrote in message
>> >> news:B135C858-6FA7-482A-B13A-D1CCF771CD80@microsoft.com...
>> >> > 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
>> >> >>
>> >> >> sandymann2.DeleteThis@mailinator.com
>> >> >> Replace @mailinator.com with @tiscali.co.uk
>> >> >>
>> >> >>
>> >> >> "Alvyn" <Alvyn.DeleteThis@discussions.microsoft.com> wrote in message
>> >> >> news:4B32EAA6-842F-4D79-9675-3378E3EDA8D6@microsoft.com...
>> >> >> > 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!A1: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
>> >> >> >>
>> >> >> >> sandymann2.DeleteThis@mailinator.com
>> >> >> >> Replace @mailinator.com with @tiscali.co.uk
>> >> >> >>
>> >> >> >>
>> >> >> >> "Alvyn" <Alvyn.DeleteThis@discussions.microsoft.com> wrote in message
>> >> >> >> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
>> >> >> >> > 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" <Alvyn.DeleteThis@discussions.microsoft.com> wrote in message
>> >> >> >> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
>> >> >> >> >> >
>> >> >> >> >> > 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
>> >> >> >> >>
>> >> >> >> >> sandymann2.DeleteThis@mailinator.com
>> >> >> >> >> Replace @mailinator.com with @tiscali.co.uk
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>