Excel Vlookup error

2
votes

I have a spreadsheet that is stored in a shared location on a website that I need to do a Vlookup on in order to gather certain data. In order to test I have a Workbook stored in my documents. I have some VBA in this workbook but this does not affect the data in question.

The Formula that I am attempting is

=VLOOKUP("activated",$A4,4,TRUE)

What I need to do is to pull data from several columns in this sheet but I have not used Vlookup in a long time so I am trying to start small. I have formatted all of the cells to general but this has not helped.

I have also tried

=VLOOKUP("activated",$A2:$A71,4,TRUE)

However I constantly get the #REF error

Putting the following will return Activated so I know that in principal the formula is correct

=VLOOKUP("activated",$A2:$A71,1,TRUE)

I have looked at the answer submitted in Excel VLOOKUP #REF Error but this has not resulted in the correction of my errors

Any help would be greatly appreciated

1
excelvlookupvba

1 Answers

2
votes

The third argument is the column that you want to return... As there is only 1 column in the range $A2:$A71 you can't put 4 as the argument...

You probably need =VLOOKUP("activated",$A2:$D71,4,TRUE)

Also you should probably use FALSE for your last argument, as this can return the next closest match, if you use TRUE.

Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

http://office.microsoft.com/en-gb/excel-help/vlookup-HP005209335.aspx