Need help with Vlookup/Index Match which I eventually need coded in vba

[Jamel]

Part 1 Vlookup inquiry -

I have a workbook I want to reference another sheet with the workbook pull in information based on the following 3 condition.

1. I want to pull in info when and id on the destination sheet matches the source sheet.
2. I want it to assign a text value when the destination has a text value in one of the columns
3. I want to assign a text value when a date in one of the columns is greater than 6/30/2015.

I need no.# 1 to supersede both no.#2 and no.#2 to supersede number 3.
In cases where none of these conditions are met, I want it to return the value "NOT EVALUATED"

Here's the formula how I have it written currently:

=IFERROR(IF(B585="VACANT","VACANT",IF(G585>DATEVALUE("6/30/2015"),"New Hire",(VLOOKUP(A585,rating,12,FALSE)))),"NOT EVALUATED")

The was it is writen now it doesn't seem to be honoring the vlookup first. I'd tried changing around
the order but that didnt work.

Part 2. I would like to convert the following formula to an index formula. I got a lot going on in the spreadsheet and vlookup takes too long to calculate.

Here's my attempt at an index formula thus far:

=IFERROR(IF(B5="VACANT","VACANT",IF(INDEX(rating!L:L,MATCH(Allstaff!A5,rating!A:A,0))=(INDEX(rating!L:L,MATCH(Allstaff!A5,rating!A:A,0)))=TRUE,(INDEX(rating!L:L,MATCH(Allstaff!A5,rating!A:A,0))),IF(G5>DATEVALUE("6/30/2015"),"New Hire"""))),"NOT EVALUATED")

The formula about doesnt seem to be honoring the datavalue portion of the formula.

Part 3. The last step is including this in my vba script.

I tried just recording a macro when I typed it and I get an error. Error states "Run - Time error '1004':
Unable to set the FormulaArray property of the Range class.

Here the vba script:


Selection.FormulaArray = _
"=IFERROR(IF(INDEX(rating!C[-84],MATCH(Allstaff!RC[-95],rating!C[-95],0))=(INDEX(rating!C[-84],MATCH(Allstaff!RC[-95],rating!C[-95],0)))=TRUE," & Chr(10) & "(INDEX(rating!C[-84],MATCH(Allstaff!RC[-95],rating!C[-95],0))),IF(RC[-94]=""VACANT"",""VACANT"",IF(RC[-89]>DATEVALUE(""6/30/2015""),""New Hire""""""))),""NOT EVALUATED"")"
Range("CR3").Select


Sorry for the long question but I've been trying to troubleshoot this for days. Any help would be
very very very much appreciated.

Jamel

 

[Mackers]

Hi

In general, the easiest way for us to solve your problem is to post an example set of data including expected results, along with a concise description of your problem.

I've had a crack at what I think you want the first part to do. I think the ordering should go like this, with your VLOOKUP() evaluating first, and then if it returns an error (i.e. if A585 is not found in the first column of the 'rating' table) then the New Hire check is done, and if that fails then the VACANT check is done, and if that fails then it returns NOT EVALUATED.

=IFERROR(VLOOKUP(A585,rating,12,0),IF(G585>DATEVALUE("6/30/2015"),"New Hire",IF(B585="VACANT","VACANT", "NOT EVALUATED")))

If you could explain what the second part is actually trying to do I can try to fix it for you. Note you have """ in one section of your formula - is this intentional?

FYI in general it is somewhat bad practice to reference entire columns like A:A rather than the actual data like A$2$:$A$1000 as it slows down some formulas to a crawl.

Hope that helps

Mackers

 

[Jamel]

Wow...


You've solve the first problem with the vlookup section.

Ok... I'll do my best at explaining what the second section is attempting to do.

Long story short, I'm trying to convert the vlookup formula you've just fixed to an
index/match formula so that it can run faster and recalculate faster when
I filter on a section of the data in the spreadsheet. I read index/match is better to use.

So everywhere you see index/match in that formula was where I use to have a vlookup formula.
I may have had attempted to change the order around when it didn't work. So this is why its not mirroring the vlookup formula.

I'm referencing data on a different sheet tab to pull in data in one of the columns where it equals
data on the sheet where I entered the formula. When that doesn't produce results(due to there not
being a match on that sheet that I'm referencing) I want it to populate the field(where the formula resides) with the word "vacant" when that word is match in a different column. And if that doesn't work I want it to look in another column to see if the hired date is greater than 6/30/2015. If it is populate the field with New Hire". I all these things don't create results, populate the column with "Not Evaluated". This is pretty much what your solution to the vlookup is now doing.

Last but not least I would like this formula to be added to vba so that I can run a routine doing this.

Hope this makes sense.

The extra quotes was not intentional. I think its what happens when I recorded copying and pasting the formula in the formula bar.

Its good to know about what's not good practices when it comes to A:A. I guess my intent for doing this is because the data always changes in length and I didnt want to have to keep on adjusting it to cover more rows so I just said cover all rows.

 

[Mackers]

INDEX and MATCH isn't that much faster than VLOOKUP in this situation, it is just much more powerful and flexible so it's good to get used to using it. All you need to do is replace VLOOKUP(A585,rating,12,0) with INDEX(rating,12,MATCH(A585,FirstColumnOfRating,0)) where FirstColumnOfRating is like A2:A100 if rating is A2:X100, for example.

I can't help with the VBA unfortunately!

 

[Jamel]

will you adjust the formula so that the if(b2="VACANT","VACANT" Is the second statement instead of the last?

=IFERROR(VLOOKUP(A585,rating,12,0),IF(G585>DATEVALUE("6/30/2015"),"New Hire",IF(B585="VACANT","VACANT", "NOT EVALUATED")))

 

[Jamel]

Never mind. I think I figured it out

 

[Jamel]

Well at least I hope. Thanks for all you help. I really appreciate it.