Correcy use of VLOOKUP

[Kapz]

Hi All,

Having trouble working out VLOOKUP - I want to enter a Cycle in D3 of the first tab and then have it display the date range which is on the next tab in D5 and E5 on the first page.

Can anyone help?

[kersplash]

Try this in D5;

Please Login or Register  to view this content.

And in E5;

Please Login or Register  to view this content.

[Shareez Saleem]

The syntax of Vlookup is:

1. Lookup Value - select one specific cell from the working sheet not from the master data
As per your workbook, you should select D3 from "Roster Cycle printout" sheet

2. Table array- Select the table from the master data (make sure to start from the column where your lookup value is available.
As per your workbook, you should select the range "A:C" from "Cycles" sheet
If cycle no. is in Column B, select C:D and so on.

3. Column index no. Select the column no where the required data is available
Here in your sheet, enter 2 for start date and 3 for End date.

4. Range look up- Always enter "0"

[Kapz]

Thanks guys,

That has worked great but now I have run into the problem where if 2 values are present it will only display the one listed first.

Is it possible to still use VLOOKUP to display multiple values if there are more than 1 result returned?

[FDibbins]

Hi, welcome to the forum

No, vlookup (and it's partner INDEX/MATCH) will only ever find the 1st match - then they stop looking.

You would need to use the INDEX/SMALL/IF array function for that - although there may be another way around that using a helper?

Perhaps upload a more representative sample file?

[FDibbins]

If a helper is OK with you (you can hide it if needed), then
Cycles D2=COUNTIF($A$2:A2,'Roster Cycle printout'!$D$3)
copied down

For the extract...
D5=IFERROR(INDEX(Cycles!B:B,MATCH(ROWS($A$1:A1),Cycles!$D:$D,0)),"")
copied down and across as needed