Using VLOOKUP to return an array of answers

[polarisking]

I want to do N VLOOKUPS and return an array, something like {#N/A,#N/A,#N/A,#N/A,#N/A,Dog,Cat,#N/A,Fish,#N/A,#N/A,Mouse}


I've tried =VLOOKUP(A$3:A$50,TableName,2,False) then Shift-Ctl-Enter to process as an array. It's coming back with only the first "action". Is there a way to return what I wrote above, e.g. {#N/A,#N/A,#N/A,#N/A,#N/A,Dog,Cat,#N/A,Fish,#N/A,#N/A,Mouse}?

 

[Sajan]

@polarisking,


Yes, there are many ways to return an array as the result of a formula.


Can you describe what you are expecting to be returned as an array, and perhaps we would be able to suggest approaches to generate such an array.


For example, something like the following will return an array. (To see the values, select the formula in a cell, and press F9.)

=SEARCH({"A";"B";"C"}, Z1:Z10)


Cheers,

Sajan.

 

[bobhc]

Good evening polarisking


Maybe the upload will help point you in the right direction


https://dl.dropbox.com/u/75495784/Correct_Formula-IfError_with_Vlookup.xlsx

 

[Sajan]

@polarisking,


You could also try something like the following, if you are trying to return values from column2, when values in column1 match some criteria:


=IF(ISNUMBER(MATCH(index(TableName,0, 1),A$3:A$50,0)), index(TableName, 0,2))


This assumes that TableName is a range with at least 2 columns.


Cheers,

Sajan.

 

[polarisking]

Thank you Sajan and bobhc for your reply. I've posted a file here


https://www.dropbox.com/sh/yficwj2a6r43b9g/TokgmomFpX showing what I need.


There are values attached to each "Set". The number of values can vary from 1 to N. Some of the values are special by dint of them being found in the table.


I'd like to be able to put an array formula next to each replicated "Set" and have it return the Value, if it's found, and a space (or some other non-error value) is it's not.


The top table is what I'm given; the bottom table is what I'd like to see. If possible, I'd like the array to be only as long as the COUNTA value of each "Set".

 

[NARAYANK991]

Hi ,


I am not clear on what you want , but is this anything like what you are looking for ?


http://speedy.sh/ePmGT/VLOOKUP-Example.xlsx


Narayan

 

[polarisking]

Narayan, kind of. Ideally, I like to put one array formula on each row and have it deliver back a variable length array that looks like the one in the example. This is fine, thank you, for now.