# Vlookup Formula not working...

1. ## 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

2. ## Re: Vlookup Formula not working...

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

3. ## Re: Vlookup Formula not working...

PERFECT... cheers mate, been stressing over this for a couple of days now!!

The only problem is it will only find the first 2 entries... after that it gives "#N/A", do you know why this may be?

Cheers

Laz

4. ## Re: Vlookup Formula not working...

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

5. ## Re: Vlookup Formula not working...

Swambo

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

rylo

6. ## Re: Vlookup Formula not working...

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

7. ## Re: Vlookup Formula not working...

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

8. ## Re: Vlookup Formula not working...

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

9. ## Re: Vlookup Formula not working...

Laz

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

rylo

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1