Vlookup Array

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
I have the following formula, that is suppose to return 711, and right now it only returns 7. Will someone offer a suggestion? Thanks

{=VLOOKUP($B$5,Table,{2,3,4},FALSE)}
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Why do you have {2,3,4}?

What does the table look like? Where in the table is the result supposed to come from?
 
Upvote 0
NBVC said:
Why do you have {2,3,4}?

What does the table look like? Where in the table is the result supposed to come from?

The table is a couple of thousand rows, with the store name being in column A and the store data after the store name, like address in b2, city in c2, state, in d2 and so on. What I am trying to do with one formula is put b,c, and d in one cell. So address, city, and state. I have them represented by numbers not to confuse the earlier post. Thanks for your help.

I also seem to remember that this question has been answered, but I can't find the post. Again, thanks
 
Upvote 0
So you want to lookup the store name and return it with the address glued to it?
Book2
ABCDE
1StoreAddr1Addr2joe's eatsjoe's eats 456 Oak St Hoboken, NJ
2the foo bar123 Main StOurTown, IA
3joe's eats456 Oak StHoboken, NJ
4adams apple farm999 666th AveLas Vegas, NV
5
Sheet3


MCONCAT is from the Morefunc add-in. The Morefunc add-in is a free download, available at: this site.

If you cannot use that add-in, you can substitute the ACONCAT function.<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> aconcat(a<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>,<SPAN style="color:#00007F">Optional</SPAN> sep<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN> = "")<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#007F00">' Harlan Grove, Mar 2002</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> y<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>

    <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">TypeOf</SPAN> a<SPAN style="color:#00007F">Is</SPAN> Range<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> y<SPAN style="color:#00007F">In</SPAN> a.Cells
            aconcat = aconcat & y.Value & sep
        <SPAN style="color:#00007F">Next</SPAN> y
    <SPAN style="color:#00007F">ElseIf</SPAN> IsArray(a)<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> y<SPAN style="color:#00007F">In</SPAN> a
            aconcat = aconcat & y & sep
        <SPAN style="color:#00007F">Next</SPAN> y
    <SPAN style="color:#00007F">Else</SPAN>
        aconcat = aconcat & a & sep
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>

    aconcat = Left(aconcat, Len(aconcat) - Len(sep))<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>
 
Upvote 0
Maybe there is something better, but this will do it.

=VLOOKUP($B$5,table,1,FALSE)&VLOOKUP($B$5,table,2,FALSE)&VLOOKUP($B$5,table,3,FALSE)

you can add space strings in between if you like.
 
Upvote 0
NBVC said:
Maybe there is something better, but this will do it.

=VLOOKUP($B$5,table,1,FALSE)&VLOOKUP($B$5,table,2,FALSE)&VLOOKUP($B$5,table,3,FALSE)

you can add space strings in between if you like.

I forgot that to do the type of array formula I had questioned in the first post I had to select 3 different cells. So the solution above is what I am using now. Thanks NBVC
 
Upvote 0
Aaron said:
I have the following formula, that is suppose to return 711, and right now it only returns 7. Will someone offer a suggestion? Thanks

{=VLOOKUP($B$5,Table,{2,3,4},FALSE)}

Care to post the expected result?
 
Upvote 0
just_jon said:
So you want to lookup the store name and return it with the address glued to it?

What I would like to have is the end user select the store, and then in a cell below it have 3 numbers (#'s the end user is familiar with that represent the address city state) return in the cell below. I thought an array vlookup would work but I guess I will have to use what NBVC suggested. Thanks Jon!!
 
Upvote 0
See my revised post above, and note Aladin's question -- he'll come up with a better solution.
 
Upvote 0
Aladin Akyurek said:
Aaron said:
I have the following formula, that is suppose to return 711, and right now it only returns 7. Will someone offer a suggestion? Thanks

{=VLOOKUP($B$5,Table,{2,3,4},FALSE)}

Care to post the expected result?

Thanks Aladin, the expected result should be 711, and the formula is returning 7 only. So the solution I am using right now is =VLOOKUP($B$5,Table,2,FALSE)&VLOOKUP($B$5,Table,3,FALSE)&VLOOKUP($B$5,Table,4,FALSE)
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top