IFNA with VLOOKUP

[elninohaginho]

Hello,

I am trying to bruteforce myself through some of the VBA obstacles (mostly because I am a beginner and don't know how to code this properly) and have just encountered one that I can't overcome.

The basic idea is the following:

My VBA macro is finding a first empty cell in column D and selects it. There are some e-mail addresses in column C that I want to run a vlookup on, so in the cell selected I set value =IFNA(VLOOKUP(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1),Functions!A1:I176,9),VLOOKUP(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1),Functions!A1:I176,8)) and autofill it in column D to run it on all of the e-mail addresses in column C.

The idea behind this formula is basically this:

A VLOOKUP is run on the currently selected cell offset by 1 column. If it finds no data in column 9 in Functions sheet, it proceeds to look for the value in column 8, which is always filled.

I have tried IFERROR and ISBLANK nested in IF, but for some reason I am still getting the N/A error when there is no data in column 9, however if there is some data in this column, I am receiving it correctly.

Can someone please be so kind and help me get my head around this error? If you can't or don't want to help me but got this far, let me at least congratulate you on your persistence and wish you a great day!

 

[steve the fish]

Firstly why are you using offset indirect and the rest? Why arent you just using a cell ref? Which cell is this formula in?

 

[steve the fish]

Ok you rarely need to select in VBA but seen as you have a selected cell you could use:

ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC[-1],Functions!R1C1:R176C9,9,0)="""",VLOOKUP(RC[-1],Functions!R1C1:R176C9,8,0),VLOOKUP(RC[-1],Functions!R1C1:R176C9,9,0))"

 

[elninohaginho]

The reason I am using offset indirect is that I don't know which cell my macro is going to end up in. I first find a first blank cell in column C, paste a list of e-mail addresses there, and then want to run a vlookup in column D for all of the e-mail addresses. This could probably be much easier done with VBA alone, but since my knowledge of it is limited, I am cheating here a bit by using a formula in the first blank cell in column D, and autofilling it for the remaining cells.

 

[elninohaginho]

I tried the code and it does some amazing things, however I am now getting the N/A error for all of the cells, regardless if the data is there or not. Without the 0s in VLOOKUP I am getting some data when it is present in column 9, but there are some incorrect entries and N/A for the rest.

 

[steve the fish]

You wont be getting na() if the data is there so there is another problem. Copy and paste here the cell to the left of the formula that produces na() and A1:A176 of the functions worksheet.

 

[elninohaginho]

I can't find a way to attach a file here, so I uploaded it to this address: https://ufile.io/3zhai I made the same setup as I have in my file, but there is one step missing that I wonder if may impact the result - the Functions sheet contains data that was copied as values from a table uploaded on a sharepoint. I have however cleared the content in those cells just in case they are not truly blank (although there is nothing in them when clicked on) but it unfortunately seems to have no effect on the error.

 

[steve the fish]

The email addresses are in column C not column A as your initial vlookup was looking at hence the error. Taking my code:

ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC[-1],Functions!R1C3:R176C9,6,0)="""",VLOOKUP(RC[-1],Functions!R1C3:R176C9,7,0),VLOOKUP(RC[-1],Functions!R1C3:R176C9,6,0))"

 

[elninohaginho]

I also noticed it a minute ago and tried to edit my previous formula, but failed miserably, and yours works like a charm! You Sir deserve a medal and the next presidency! Thank you SOOO MUCH!