Excel, Returning Two Values

Cotta

Hi All,

I have a table within Excel with a simple formula that allows me to search a link a name within that table with another table and to return to corresponding value. Below is the formula I use:

=VLOOKUP(A40,$I:$N,3,FALSE)

However my problem with this is that it only returns the valued linkled to the first name and sometimes there are more than one matching name and I would like to return all such values - is this pobbile?

thebigbosh

simplest way would be to perform the vlookup from the original first table to the second and then pivot for results, excluding the n/a bits

Otherwise, write a looping macro that searches through the original first table for the values and populates the second. If you tell the macro to search til the end of the table, and record any duplicates, then you could populate values from the duplicates in the fields to the right in the second table. First options seems a lot easier!!

InsideInsurance

Not with vLookUp, it is designed to only bring back the first one.

By the sounds of it you are getting more into the territory of a database than a spreadsheet. There are none ideal ways to do it but would help to have more context.

thebigbosh

I got curious and continued searching. Here's some text from another site:

For 2007+ use Data > From Other Sources > From Microsoft Query:

choose Excel File and select your 1st excel
choose columns (if you don't see any list of columns, make sure to check Options > System Tables)
edit the Data > Connections > choose your new connection > Properties > Definition > Command text
you can now edit this Command text as SQL, i tried implicit joins, "inner join", "left join" and unions:

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2

If you know your way around SQL you might well get this to work

Stompa

Maybe?

http://office.microsoft.com/en-gb/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx