Hi everyone,
I have been struggling for a few days with this. I am actually using google sheets, but it is the basic vlookup functionality I am hoping to get help with.
An API delivers data to tabs “DT Export staff by title” and “DT Holiday Export”. I use queries to import the required data into tabs “Avonmouth Hols” and “Avonmouth Staff”.
Holidays are delivered row by row and a start and end date are given. I want to consolidate this information into a calendar view, that shows all holidays per person booked. Tab “Avonmouth Staff” shows this attempt.
This is simply the latest of many attempts hence why it looks slightly long winded. The problem is that when the vlookup searches for the employee id – it will find many entries, but not always will there be a holiday for that date. Therefore this formula misses most holidays. And so I need the vlookup to be able to skip blank cells.
My best attempt was:-
=VLOOKUP($A2,SORT('Copy of Create Hol Cal'!$A$1:$CW,MATCH(H$1,'Copy of Create Hol Cal'!$A$1:$CW$1,0),FALSE),MATCH(H$1,'Copy of Create Hol Cal'!$A$1:$CW$1,0),FALSE)
This formula is a copy from another google sheet – and here I am using a match to tell the vlookup which column to return and I am using sort to put the “1” to the top so that the vlookup always find the highest value.
The problem with this is that it crashes the sheet – there are too many calculations.
Can anyone tell me a neater way for the vlookup to ignore blank cells, in a dynamic search such as I require?
Let me know if I have no explained things clearly
Thank you in advance for any assistance you can provide
I have been struggling for a few days with this. I am actually using google sheets, but it is the basic vlookup functionality I am hoping to get help with.
An API delivers data to tabs “DT Export staff by title” and “DT Holiday Export”. I use queries to import the required data into tabs “Avonmouth Hols” and “Avonmouth Staff”.
Holidays are delivered row by row and a start and end date are given. I want to consolidate this information into a calendar view, that shows all holidays per person booked. Tab “Avonmouth Staff” shows this attempt.
This is simply the latest of many attempts hence why it looks slightly long winded. The problem is that when the vlookup searches for the employee id – it will find many entries, but not always will there be a holiday for that date. Therefore this formula misses most holidays. And so I need the vlookup to be able to skip blank cells.
My best attempt was:-
=VLOOKUP($A2,SORT('Copy of Create Hol Cal'!$A$1:$CW,MATCH(H$1,'Copy of Create Hol Cal'!$A$1:$CW$1,0),FALSE),MATCH(H$1,'Copy of Create Hol Cal'!$A$1:$CW$1,0),FALSE)
This formula is a copy from another google sheet – and here I am using a match to tell the vlookup which column to return and I am using sort to put the “1” to the top so that the vlookup always find the highest value.
The problem with this is that it crashes the sheet – there are too many calculations.
Can anyone tell me a neater way for the vlookup to ignore blank cells, in a dynamic search such as I require?
Let me know if I have no explained things clearly
Thank you in advance for any assistance you can provide
