VLOOKUP to return multiple corresponding values

[n1k]

Im trying to use VLOOKUP to look up one value and return multiple corresponding values.

I have the following and can return the first and last col by entering =VLOOKUP(A2,A2:B7,2,FALSE) and changing the FALSe to TRUE but I need to be able to return them all.

Ive also had a look at the INDEX function which I don;t know much about but woth this I can only return one of them

=INDEX($A$2:$C$8,SMALL(IF($A$2:$A$8=$A$2,ROW($A$2:$A$8)),ROW(1:8)),2)

A B
10/Jan Taylor Nelson Sofres
10/Jan test
10/Jan test 2
10/Jan test 3
10/Jan test 4
10/Jan test 5

Ive spent all day trying to work this out if anyone can shed any light it would be appreciated!

Thanks, hopefully

[NBVC]

That formula should work... you made it an array formula and so you just need to confirm it with CTRL+SHIFT+ENTER keys not just ENTER... you will see { } appear around the formula.

The formula will not return the first match at B2, though... to do that revise formula to:

[dode]=INDEX($A$2:$C$8,SMALL(IF($A$2:$A$8=$A$2,ROW($A$2:$A$8)-ROW($A$2)+1),ROW(1:8)),2)[/code]

and confirm with CSE keys again....then copy down

[n1k]

thats perfect thankyou!

Do you know how I would change the formula to show a blank cell if it brings back #NUM! though?

thanks again

[NBVC]

Try:

Please Login or Register  to view this content.

[n1k]

Hi again, thankyou for all your help on this.

I think i prob explained myself wrong though as what I am using it for is to look up dates and return the the info I have in several columns into a calendar, this works perfectly for what I gave you on the 10th of Jan but when I am trying to manipulate it for other dates it is not returning anything, sorry I have worked with VLOOKUP quite a bit but am not sure with this INDEX formula.

I was initially doing a VLOOKUP and using the date in the calendar as the lookup array then looking at the DATA page for the table array which holds some of the following information, this worked fine other than I couldn;t return values for several dates but now I find myself not able to look for dates other than the 10th.

Please help!

Date Company
10/Jan Taylor Nelson Sofres
10/Jan Euromoney
10/Jan gcap
23/Jan Chrysalis
29/Jan Future
29/Jan Next Fifteen Comm.
30/Jan Euromoney

[NBVC]

Can you post a zipped copy of the workbook (XL2003 or earlier)? It's hard to understand your requirement.

[n1k]

Hi, sorry have attached the zip, there are 2 worksheets 'Jan', which has the calendar in, and 'Companies' where I am entering the information that I want to return in the dates in the calendar.

Thanks again for your help

Nikki

btw my firewall prohibits me opening the attachments from this site so if you could outline in the body of this mail that would be great

[NBVC]

First change formula in Companies!E2 to:

Please Login or Register  to view this content.

confirm with CSE keys and copy down.

Then in the Calendar, change all the dates to "real dates" and format as Custom >> dd/mmm (this will make it appear as you have it now).

Then in say H13 for 10/Jan enter formula:

Please Login or Register  to view this content.

The $G$12 references the date input in H13.

Confirm with CSE keys and copy down 3 cells.

Then select H13 and copy formula from the formula bar at top and paste it in the next date cell. Change the $G$12 to match your current date selection and confirm again with CSE and copy down.

Repeat this step for all dates.

Hope that helped.

[n1k]

that's perfect, thank you so much for your help!

[n1k]

Hi, that has all worked perfectly except that my fomula that ive set the conditional formatting on isn;t returning the correct even for the company, ive attached the file again, the problem cells are in the Jan worksheet C37. It should be returning the cell C6 in the companies sheet

Nearly have it all!

thankyou

[NBVC]

Since you have 5 colours to choose from you can't use Conditional Formatting to get all 5 possibilities....you will need VBA.

so first, right click on the Jan sheet tab and select View Code. Paste the following code which was gotten and adjusted from here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

Please Login or Register  to view this content.

Then in the Jan tab, to get that data you will have to copy over the same formula I gave you for the adjacent data and change the last 2 in the formula to a 3. Then reconfirm and copy down....

You will need to repeat for all days.

In case you need it and you can find a way to download, I've attached a sample showing the Jan 10 and Jan 29 dates completed.

[n1k]

thank you so much for all your help with this that all works perfectly

Nikki