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)}
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Why do you have {2,3,4}?

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

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
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
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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>
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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.
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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?
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
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!!
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
See my revised post above, and note Aladin's question -- he'll come up with a better solution.
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,351
Messages
5,595,646
Members
414,005
Latest member
BradWarr

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
Top