presenting array lists returned by VLOOKUP in one cell

[twelsh37]

Hi.

I've had a good search to try to figure this one out...no joy!

Please can you have a look at this spreadsheet - return values for VLOOKUP are giving me grief… The problem is that I want the VLOOKUP function to return an array AND display ALL the values in the cell where the function is (as a list). Currently when I return arrays using VLOOKUP only the first value in the list is displayed in the cell.

This problem can be seen in the attached spreadsheet. If you look on the first worksheet (Citrix-IMS IXE ACLs) in cell E2 it can be seen that the value 'a-users' (the first value in the returned list) is being returned by the VLOOKUP function. What I would like this function to return the entire list of 'managment_subnets' (yep… management is spelt wrong - I didn't write the ACLs) as found the range of cells that are being looked at by the VLOOKUP function in the second worksheet (Firewall parameters) in column cells E2 to E5.

I would like display the values in the array returned by the VLOOKUP function (in a single cell) in the format shown here:

a_Users
a_Support
Mgmt_Sub
mngr_LAN

example_FW_spreadsheet.zip

Hope this makes sense!?...

Thanks,

Tom

[SamuelT]

Hi,

Not a perfect solution as you lose your VLOOKUP in column F of Firewall parameters, but you could always enter the whole list into the one cell, using ALT+RETURN to give a line break.

This will return your entire list as your desired (make sure Wrap Text is on), but as I said you do lose the other bit of VLOOKUPery on the other sheet. I guess if it's important you could set up another sheet/area for this piece of data.

HTH,

SamuelT

[ChemistB]

The only way I can think to do that would be to use the vlookup array elsewhere into multiple cells as it's intended and then move the multiple values into the specified cell with

Please Login or Register  to view this content.

Could something like that work for you?

ChemistB