Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Help with Excel lookup function please

I have an array - say A2:F20 which contains different names of 6 types of
referral source, where the 6 names in row 2 are the titles of the types and
the names are listed in the appropriate columns below each title.

Is there a formula I can use in Excel so that if I type a the name of a
referral source in say cell A22, cell B22 will return the appropriate type
title.

I have looked up help on using lookup tables but none of the functions seem
to provide what I want, although I can't believe it can't be done!

Thanks,

V

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Help with Excel lookup function please

Try this *array* formula:

=INDEX(A2:F2,MAX(IF(A3:F20=A22,COLUMN(A:F))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Victor Delta" wrote in message
...
I have an array - say A2:F20 which contains different names of 6 types of
referral source, where the 6 names in row 2 are the titles of the types

and
the names are listed in the appropriate columns below each title.

Is there a formula I can use in Excel so that if I type a the name of a
referral source in say cell A22, cell B22 will return the appropriate type
title.

I have looked up help on using lookup tables but none of the functions

seem
to provide what I want, although I can't believe it can't be done!

Thanks,

V


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Help with Excel lookup function please

"Ragdyer" wrote in message
...
Try this *array* formula:

=INDEX(A2:F2,MAX(IF(A3:F20=A22,COLUMN(A:F))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


Many thanks. Am I right in thinking that the columns of names must be in
alphabetical order too?

V

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Help with Excel lookup function please

"Victor Delta" wrote in message
...
"Ragdyer" wrote in message
...
Try this *array* formula:

=INDEX(A2:F2,MAX(IF(A3:F20=A22,COLUMN(A:F))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


Many thanks. Am I right in thinking that the columns of names must be in
alphabetical order too?


Actually, your formula does not seem to give consistent results - with or
without the data in alphabetical order. Also, if there is no match I would
prefer an error code or something to indicate this.

Am I doing something wrong or is it
your formula????

Thanks,

V

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Help with Excel lookup function please

Sorry, I didn't test for non-existent matches.

You're right, with no match found, the original formula always returns the
first column, although it *does work* when matches are present ... with no
sorting necessary.
If duplicates matches exist, the column of the last match is returned.

Try this formula, also an *array* formula, if there is the possibility of
non-existent matches ... where I included a text statement as part of the
error trap:

=IF(SUM(--ISNUMBER(SEARCH(A22,A3:F20))),INDEX(A2:F2,
MAX((A3:F20=A22)*(COLUMN(A:F)))),"No Match")

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

As in the original formula, if duplicate matches exist, the column of the
*last* match is returned.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Victor Delta" wrote in message
...
"Victor Delta" wrote in message
...
"Ragdyer" wrote in message
...
Try this *array* formula:

=INDEX(A2:F2,MAX(IF(A3:F20=A22,COLUMN(A:F))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,

instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


Many thanks. Am I right in thinking that the columns of names must be in
alphabetical order too?


Actually, your formula does not seem to give consistent results - with or
without the data in alphabetical order. Also, if there is no match I would
prefer an error code or something to indicate this.

Am I doing something wrong or is it
your formula????

Thanks,

V




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Help with Excel lookup function please

"Ragdyer" wrote in message
...
Sorry, I didn't test for non-existent matches.

You're right, with no match found, the original formula always returns the
first column, although it *does work* when matches are present ... with no
sorting necessary.
If duplicates matches exist, the column of the last match is returned.

Try this formula, also an *array* formula, if there is the possibility of
non-existent matches ... where I included a text statement as part of the
error trap:

=IF(SUM(--ISNUMBER(SEARCH(A22,A3:F20))),INDEX(A2:F2,
MAX((A3:F20=A22)*(COLUMN(A:F)))),"No Match")


Many thanks - that's a great improvement.

The only funny thing now is that if you enter any letter or letters that
correspond with part of one of the names, you get the first column selected
instead of 'No Match'. Don't suppose it is possible to fix as well this
please?

Thanks,

V

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Help with Excel lookup function please

Try this:

=IF(SUM(--(A3:F20=A22)),INDEX(A2:F2,
MAX((A3:F20=A22)*(COLUMN(A:F)))),"No Match")

Still an *array* formula.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Victor Delta" wrote in message
...
"Ragdyer" wrote in message
...
Sorry, I didn't test for non-existent matches.

You're right, with no match found, the original formula always returns

the
first column, although it *does work* when matches are present ... with

no
sorting necessary.
If duplicates matches exist, the column of the last match is returned.

Try this formula, also an *array* formula, if there is the possibility

of
non-existent matches ... where I included a text statement as part of

the
error trap:

=IF(SUM(--ISNUMBER(SEARCH(A22,A3:F20))),INDEX(A2:F2,
MAX((A3:F20=A22)*(COLUMN(A:F)))),"No Match")


Many thanks - that's a great improvement.

The only funny thing now is that if you enter any letter or letters that
correspond with part of one of the names, you get the first column

selected
instead of 'No Match'. Don't suppose it is possible to fix as well this
please?

Thanks,

V


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Help with Excel lookup function please

"Ragdyer" wrote in message
...
Try this:

=IF(SUM(--(A3:F20=A22)),INDEX(A2:F2,
MAX((A3:F20=A22)*(COLUMN(A:F)))),"No Match")

Still an *array* formula.


Many thanks,

V

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Help with Excel lookup function please

You're welcome, and thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Victor Delta" wrote in message
...
"Ragdyer" wrote in message
...
Try this:

=IF(SUM(--(A3:F20=A22)),INDEX(A2:F2,
MAX((A3:F20=A22)*(COLUMN(A:F)))),"No Match")

Still an *array* formula.


Many thanks,

V


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Help with Excel lookup function please

"Ragdyer" wrote in message
...
You're welcome, and thank you for the feed-back.
--
Regards,

RD


Oh dear, I've tried moving the table to another part of my spreadsheet -
changing all the cell references correctly - and instead of matches, it now
only outputs #REF! errors.

Will it only work in the first few columns?

V



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Help with Excel lookup function please

You probably revised the Column() function incorrectly!

That function is simply returning a number to the Index() function, which
deals with *relative* locations.

The Column() function has *nothing* to do with location.
Use it just to specify the *number of columns* in the array.
If you still have 6 columns in the array, *Don't* change it at all.
Leave it "Column(A:F)", no matter where you move to.


For example, if your new location is J12 to O30 (still 6 columns), use this:

=IF(SUM(N(J13:O30=A22)),INDEX(J12:O12,MAX((J13:O30 =A22)*(COLUMN(A:F)))),"No
Match")


If it's this - J12 to R30 (9 columns), use this:

=IF(SUM(N(J13:R30=A22)),INDEX(J12:R12,MAX((J13:R30 =A22)*(COLUMN(A:I)))),"No
Match")

Don't forget - still an *array* formula - use CSE.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Victor Delta" wrote in message
...
"Ragdyer" wrote in message
...
You're welcome, and thank you for the feed-back.
--
Regards,

RD


Oh dear, I've tried moving the table to another part of my spreadsheet -
changing all the cell references correctly - and instead of matches, it now
only outputs #REF! errors.

Will it only work in the first few columns?

V


  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default Help with Excel lookup function please

"RagDyeR" wrote in message
...
You probably revised the Column() function incorrectly!

That function is simply returning a number to the Index() function, which
deals with *relative* locations.

The Column() function has *nothing* to do with location.
Use it just to specify the *number of columns* in the array.
If you still have 6 columns in the array, *Don't* change it at all.
Leave it "Column(A:F)", no matter where you move to.


For example, if your new location is J12 to O30 (still 6 columns), use
this:

=IF(SUM(N(J13:O30=A22)),INDEX(J12:O12,MAX((J13:O30 =A22)*(COLUMN(A:F)))),"No
Match")


If it's this - J12 to R30 (9 columns), use this:

=IF(SUM(N(J13:R30=A22)),INDEX(J12:R12,MAX((J13:R30 =A22)*(COLUMN(A:I)))),"No
Match")

Don't forget - still an *array* formula - use CSE.
--

HTH,

RD


Very many thanks again - you are absolutely correct and that's exactly the
error I had made.

As an interim solution, I had simply added another worksheet and hence put
everything back in Cols A to B. But I will now use your solution.

Regards,

V

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default Help with Excel lookup function please

Glad you got it all sorted out.

Appreciate the feed-back, which will add info to the archives.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Victor Delta" wrote in message
...
"RagDyeR" wrote in message
...
You probably revised the Column() function incorrectly!

That function is simply returning a number to the Index() function, which
deals with *relative* locations.

The Column() function has *nothing* to do with location.
Use it just to specify the *number of columns* in the array.
If you still have 6 columns in the array, *Don't* change it at all.
Leave it "Column(A:F)", no matter where you move to.


For example, if your new location is J12 to O30 (still 6 columns), use
this:

=IF(SUM(N(J13:O30=A22)),INDEX(J12:O12,MAX((J13:O30 =A22)*(COLUMN(A:F)))),"No
Match")


If it's this - J12 to R30 (9 columns), use this:

=IF(SUM(N(J13:R30=A22)),INDEX(J12:R12,MAX((J13:R30 =A22)*(COLUMN(A:I)))),"No
Match")

Don't forget - still an *array* formula - use CSE.
--

HTH,

RD


Very many thanks again - you are absolutely correct and that's exactly the
error I had made.

As an interim solution, I had simply added another worksheet and hence put
everything back in Cols A to B. But I will now use your solution.

Regards,

V



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 Lookup function Newfie809 Excel Worksheet Functions 3 November 12th 07 07:06 PM
Lookup function Closed files Excel [email protected] Excel Worksheet Functions 3 January 12th 07 05:41 PM
advanced use of the Excel lookup function Norm Excel Discussion (Misc queries) 2 October 12th 06 01:27 PM
Lookup function in Excel Feldy Excel Worksheet Functions 4 February 17th 05 05:25 PM
IF/ LOOKUP FUNCTION - Excel 2000 Chandrashekhar Excel Worksheet Functions 3 November 5th 04 12:02 PM


All times are GMT +1. The time now is 04:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"