Vlookup Formula not working...

Hi guys,

Just wondering if someone could have a look at the attached workbook. I am trying to build a Holiday Booking spreadsheet and am using multiple VLookups (Not sure if its the most efficient, so that could be my problem). There are two Vlookup arrays (The first looks at the raw data and sorts it into days, the second looks at this and sorts into shifts) and the second VLookup will not display the result, just brings up "#NUM!". I have tested the search as a normal VLOOKUP and it works fine... Not sure what i am missing.

Any help on this would be much appreciated

Cheers

Laz

Hi

There's probably a better way to go about this (no I'm not volunteering anything), but this may get you going.

Days!A2: =IF(ROW()-1>COUNTIF(VLookup!$B$1:$B$5,Days!$A$1),"",VLOOKUP($A$1,INDEX(Holidays,SMALL(IF($A$1=INDEX(Holidays,,1),ROW(Holidays)-MIN(ROW(Holidays))+1,""),ROW(1:1)),,1),3,FALSE))
Days!B2: =IF(ROW()-1>COUNTIF(VLookup!$B$1:$B$5,Days!$A$1),"",VLOOKUP($A$1,INDEX(Holidays,SMALL(IF($A$1=INDEX(Holidays,,1),ROW(Holidays)-MIN(ROW(Holidays))+1,""),ROW(1:1)),,1),2,FALSE))

Array enter in each of those cells, and copy down. You should find that A5:B9 comes back with blanks.

Now if you check Shifts!B3 and shifts!B12, they will come back with swambol.

As an aside, if you put
Days!A2: =IF(ROW()-1>COUNTIF(VLookup!$B$1:$B$5,Days!$A$1),"",SMALL(IF(VLookup!$B$1:$B$5=Days!$A$1,VLookup!$D$1:$D$5,""),ROW()-1))
also array entered, then it will bring back 08:00 rather than 08:30.

Days!B2: =IF(ROW()-1>COUNTIF(VLookup!$B$1:$B$5,Days!$A$1),"",INDEX(VLookup!$C$1:$C$5,SUMPRODUCT(--(VLookup!$B$1:$B$5=Days!A1),--(VLookup!$D$1:$D$5=Days!G2),ROW(VLookup!$B$1:$B$5))))
This is not array entered and should bring back the relevant item to match.

HTH

rylo

Pl find the attached file.Array formulas are in Days Sheet. One in A2 and other in C2 cell.You can drag.

Swambo

Can you update your example file with the revised formulas, and show where you are having the problem please.

rylo

Thanks,

If you look on "Shifts", you can see it is only finding 2 entries, when there are clearly more than that.... Think excel has a mind of is own sometimes!!

Thanks again

Laz

Laz

Try this in shifts!B12: =INDEX(INDEX(Day1,,2),SMALL(IF($A$12=INDEX(Day1,,1),ROW(Day1),""),ROW()-11)-1)

Array entered. Then copy down. Not sure what is going on with the vlookup here, but this does at least work, and it suits my way of thinking

rylo

rylo your a genius!!

Thanks buddy, this works perfect!!

Until the next time excel decides not to work for no reason (which will be sooner rather than later) lol

Thanks again

Laz

Laz

If this is resolved, can you please go back and mark the post as SOLVED please.

rylo