Formula Explanation

[reberryjr]

I enter this formula in excel and get the results that I want. When I put the macro recorder version into a module and run it, the code works, but is structured completely differently and doesn't make sense to me. Can someone help explain this?

Formula Directly in WS
=INDEX(Bios!J:J,MATCH('Client Summary'!D2,Bios!E:E,0))

Formula From Macro Recorder
=INDEX(Bios!C[4],MATCH('Client Summary'!RC[-2],Bios!C[-1],0))

What doesn't make sense to me is, in the first formula, the first column reference I make is to column J (the 10th column), but the macro recorder version shows column 4. The only 4th column that I leverage, is from the Client Summary sheet, but it appears that the 4th column from the Bios sheet is being leveraged, which makes no sense to me. Also, when I recorded the macro, I never went back a column.

Like I said, the macro recorder version works, but the structure doesn't make sense to me, and I want to understand the code that I'm using.

 

[kbrummert]

Looks like your formula is in F2, and all the formula references are in relation to it in terms of columns...so RC[-2] is 2 columns back from F2.

https://pagecommunication.co.uk/2014/06/02/using-r1c1-reference-style-excel/

 

[reberryjr]

The -2 makes sense now, but the rest remains a mystery to me. For example, where the C[-1] comes into play. I don't leverage a field that is a column to the left of anything.

 

[MARK858]

Your active cell (or at least the cell you are putting the formula in) is in column F and C[-1] is one column to the left so column E.

=INDEX(Bios!J:J,MATCH('Client Summary'!D2,Bios!E:E,0))

=INDEX(Bios!C[4],MATCH('Client Summary'!RC[-2],Bios!C[-1],0))

Perhaps the link below is clearer for you

https://excelchamps.com/blog/r1c1-reference-style/

 

[reberryjr]

The formula is being entered in the 6th column of Client Summary sheet. I'm not referencing the 5th column of that sheet in this formula.

The value being extracted is in the 10th column of the Bios sheet. I'm not referencing the 9th column of that sheet either.

The code works, but it just doesn't make any sense to me.

 

[MARK858]

It doesn't matter what sheet you are putting the formula in, it is the relative position of the cells on any sheet to that cell.

 

[reberryjr]

Ok, so let me see if I understand this. I copied the column headers from the Source Sheet, onto the Target Sheet to help me. (Nothing like trying to teach myself VBA, to make me feel like an idiot!)

Source Sheet = Where the value is to be returned to.
Target Sheet = Where the value will be retrieved from.

=INDEX(Bios!C[4],MATCH('Client Summary'!RC[-2],Bios!C[-1],0))

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Today[/TD]
[TD]Updated[/TD]
[TD]Status[/TD]
[TD]Key[/TD]
[TD]Client ID[/TD]
[TD]First[/TD]
[TD]Last[/TD]
[TD]Suffix[/TD]
[TD]Name[/TD]
[TD]Nickname[/TD]
[/TR]
[TR]
[TD="align: right"]01/13/18[/TD]
[TD="align: right"]01/12/18[/TD]
[TD]Active[/TD]
[TD="align: right"]1[/TD]
[TD]TJ1[/TD]
[TD]Tom[/TD]
[TD]Jones[/TD]
[TD]Sr.[/TD]
[TD]Tom Jones Sr.[/TD]
[TD]Tommy[/TD]
[/TR]
[TR]
[TD="align: right"]01/13/18[/TD]
[TD="align: right"]01/12/18[/TD]
[TD]Active[/TD]
[TD="align: right"]2[/TD]
[TD]ND2[/TD]
[TD]Nancy[/TD]
[TD]Drew[/TD]
[TD][/TD]
[TD]Nancy Drew[/TD]
[TD]Nancy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Today[/TD]
[TD]Updated[/TD]
[TD]Status[/TD]
[TD]Client ID[/TD]
[TD]Name[/TD]
[TD]Nickname[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ND2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

4 = Position on the Target Sheet of the Value I will be matching against.
-2 = Position of the match value, from where the formula is being entered.
-1 = Position of the match value, on the Source Sheet, from the match value on the Target Sheet.

Is this accurate?

 

[MARK858]

As far as I can tell your wording is correct so I think you have it.

Just to be sure think of Bios!C[4] being in 2 parts Bios! and C[4].

Then imagine you are putting the formula in cell F2 of a third sheet.

Now with the C[4] think that the cell with the formula is in in column F so column 6, we are telling it that we want it to reference column J so we add 4 as J is the 10th column. So now we have column J.

But hey we don't want column J on that sheet! so then we add the instruction on what sheet we are referring to by putting the Bios! in front.

So now we have column J on sheet Bios.

Most of the time when doing an Index Match your columns for the Lookup value and the Return value are fixed and so if you made them absolute then you would probably have understood what you were looking at easier as you would have got...

"=INDEX(Bios!C10,MATCH('Client Summary'!RC[-2],Bios!C5,0))"

or if the formula is on the Client Summary sheet then just the below would do.

"=INDEX(Bios!C10,MATCH(RC[-2],Bios!C5,0))"

Clearer?

 

[reberryjr]

I believe it does. Thank you! I've noticed that the macro recorder doesn't always make it easier to understand, which is problematic for me, since I don't want to have to record every macro. Instead, I'd like to type them out, which requires understanding the syntax.

Now I get to figure out how to add MAX to some of these, as well as to refer to sheets with dynamic names. Good times!

I appreciate your help!

 

[MARK858]

Thank you! I've noticed that the macro recorder doesn't always make it easier to understand, which is problematic for me, since I don't want to have to record every macro.

You are welcome but in this case the recorder IMHO does produce the correct option as R1C1 notation makes it easier to adjust (especially when using variables) compared to A1 notation when programming as you will realise when you do more and so it is worth getting familiar with R1C1 notation but having said that you could have just used...

Sheets("Client Summary").Range("F2").Formula = "=INDEX(Bios!$J:$J,MATCH(D2,Bios!$E:$E,0))"

or even for instance

Sheets("Client Summary").Range("F2:F100").Formula = "=INDEX(Bios!$J:$J,MATCH(D2,Bios!$E:$E,0))"

The D2 will increment for each different row.