Hi Aamir,
Not sure exactly what you're after (haven't seen your workbook), but perhaps
this is helpful:
{
=TRANSPOSE(INDEX(TRANSPOSE(ColumnsOfTableYouWantReturned),,MATCH(WhatValueTo
Lookup,LookupRange,0))) }
If your values to return are all numbers, you can use this instead:
{
=INDEX(--ColumnsOfTableYouWantReturned,MATCH(WhatValueToLookup,LookupRange,0
)) }
If your values to return are all text, you can use this one instead:
{
=INDEX(ColumnsOfTableYouWantReturned&"",MATCH(WhatValueToLookup,LookupRange,
0)) }
The reason for the TRANSPOSEs, -- and &"" in each of the above solutions is
to convert the lookup table to an array. Index can return an entire row or
column at once from an array, but not from a range.
Enter the above array formulas (between the braces) over several columns and
confirm entry with ctrl-shift-enter. It will essentially return the whole
matching record or as many columns of it as you specify. If LookupRange is
sorted, you may want to change the ,0 near the end of the formula to 1 (for
ascending order) or -1 (for descending order). The upside to that change is
it will make the lookup very fast. The downside is that if there is not an
exact match, it will return a neighboring record instead.
The above formulas will return the same values that a series of VLOOKUPs in
each column would achieve, but faster.
Sincerely,
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Aamir Shahzad
Sent: Wednesday, October 24, 2012 11:10 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Re: Help need to get multiple values by
vlookup
I have registered thanks as well
On Thu, Oct 25, 2012 at 11:06 AM, ashish damor <damor.ash...@gmail.com>
wrote:
Dear Aamir,
The registration is free and it does not take even 5 minutes to register.
On Thu, Oct 25, 2012 at 10:52 AM, Aamir Shahzad
<aamirshahza...@gmail.com> wrote:
> Dear Rajan,
>
> If you have file plz share with us, your required link want to register
> first.
>
>
>
> On Thu, Oct 25, 2012 at 8:23 AM, Rajan_Verma <rajanverma1...@gmail.com>
> wrote:
>>
>> Hi
>>
>> Download the file from here
>>
>> http://www.excelfox.com/forum/f12/vlookup-return-multiple-values-4/
>>
>> Thanks
>> Rajan.
>>
>> On Wednesday, 24 October 2012 22:51:01 UTC+5:30, saravanan R wrote:
>>>
>>> Hi xlts,
>>>
>>> Please help me, on how to get the all the associated items In the range
>>> where it as more than 1 lookupvalue.
>>>
>>> Thanks,
>>> Saravanan
>>
>> --
>> Join official facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES (1120+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
>> not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> 6) Jobs posting is not allowed.
>>
>> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> To unsubscribe from this group, send email to
>> excel-macros+unsubscr...@googlegroups.com
<mailto:excel-macros%2bunsubscr...@googlegroups.com> .
>>
>>
>
>
>
>
> --
>
> Regards,
>
> Aamir Shahzad
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com
<mailto:excel-macros%2bunsubscr...@googlegroups.com> .
>
>
--
Thanking you.
With regards,
Ashish Damor
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com
<mailto:excel-macros%2bunsubscr...@googlegroups.com> .
--
Regards,
Aamir Shahzad
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
