lookup formula to return value for more than one match?

[benneesham]

Hello

Is it possible to make a vlookup formula that will return more than one value from the column it is searching?

I have a list of entries on one workbook which also exist in another workbook with additional data in the next column.
The entries may appear more than once in the second workbook with different additional data which I need to return to the first workbook if possible?
Can a formula be written to do this or do I have to reorder the workbooks?

Regards

Ben Neesham

 

[shades]

Howdy, and welcome to the board. It won't work directly, but without seeing your set up, it is a little difficult to point you in the right direction. Can you provide a sample? Put workbook 1 on Sheet1 and workbook 2 on Sheet2. Then indicate what you expect to see.
________
Tracford

 

[benneesham]

Hello again

I have attached a spreadsheet with a sample of the data, its all under the freedom of information act, no sensitive data therein.

I would like to match up 'birthwards' to schools.

The first worksheet is a list of schools. The second worksheet in a list of birthwards, which are geographical areas near schools that record birth numbers to provide the schools with an uptake of children.

Each school has a unique numerical identifier code, the NIDFEE number.
I wanted to use this code to see how many birthwards from the second worksheet were linked to each school. Schools can have more than one, and they change frequently.

These tables are from an Access DB that is being rebuilt so we dont have the functionality of querying for a while and have to run reports on Excel for a bit.

I hope this is clearer.

Regards

Benn

 

[benneesham]

sorry, forgot to attach file.

Regards

Benn

 

[Brianwarnock]

It is simple to show which school is linked to which birthward on the second sheet , in D2 put =VLOOKUP(B2,School!A:B,2) and fill down.

How you can put multiple fresh entries onto sheet school I don't know.

Brian

 

[benneesham]

Hello all

Thank you for your help so far we have found a resolution to our problem now.

Regards

Benn

 

[Rabbie]

It would be helpful if you could publish your solution

 

[Brianwarnock]

It would be helpful if you could publish your solution

As he has been on since you posted and has not obliged it would appear that he does not want to share his solution.

Brian

 

[unmarkedhelicopter]

hmmm ! how 'bout that for a sense of community ...

 

[DanG]

If he told you, he'd have to kill you (_x_)

 

[benneesham]

Hi again

sorry hectic time here.
my solution didn't involve Excel at all. our Access DB had buckled under recent pressure and not giving accurate data so whilst our new SQL DB was being implmented we had to run forecasts from Excel. one small part was this area reference. in Access it was a many to many relationship which looked impossible to do in a simple Excel export/ report so we created a small Access DB on local desktop, imported the data, created link table to handle N:N relationship and exported whatever results we needed from the new queries back to Excel for inclusion in our temporary reports.
long way around but all over now, new DB in place to start querying from.
Thanks again. Complex forecasting reports in Excel are quite strenuous.

Regards
Benn