Vlookup and match functions

Hello,
I need some help using the vlookup and match (maybe also the index function) with this excel problem I'm having. I also tried using a couter in my VBA code, but the counter stuff got really messy.

Thank you in advance! Send me a direct message if you want to see the excel file. Sorry about the formatting.


=VLOOKUP($AH7,AH6:AH504,MATCH(AK$4,$AK4:$AK21,0),FALSE)& VLOOKUP(AI6,AI$6:AI$504,MATCH(AN3,AN3,0),FALSE)&VLOOKUP(AJ7,AJ$6:AJ$504, MATCH(AN3,AN3,0),FALSE)


Avg X Y Z
172 54 02 1
74 54 02 3
168 54 03 1
78 54 03 3


Therefore I want to find the value 74 (avg) and put it in this position, and so forth from the x, y, z coordinates to give the avg. My spreadsheet has 504 rows and 49 columns. I want to either use vlookup and match functions, or I need help writing some VBA code.

blank blank 1 2 3
54 3 blank 74 78
54 2 blank blank blank
54 1 blank blank 168

selected data for avg matching.xlsm

Here is my file. I have written some VBA code and my comments are in there as well. I took one VBA class in college, so I'm fairly new to this program. If you have any more questions please reply to my post!!

average vlookup.docx

I have attached another file, this is an example of what I want the code to do.

To clarify, the spot for 74 is coordinates x=54, y = 2 and z =3. I manually filled in some other averages to show you how I want my spreadsheet to look when I have the code figured out.

Thank you!

That was really helpful! That's exactly what I wanted my code to do. One more question...you added some code to the RibbonX VBA section.

Can you explain what this does-->

'Entry point for RibbonX button click
Sub ShowATPDialog(control As IRibbonControl)
Application.Run ("fDialog")
End Sub

'Callback for RibbonX button label
Sub GetATPLabel(control As IRibbonControl, ByRef label)
label = ThisWorkbook.Sheets("RES").Range("A10").Value
End Sub

That RibbonX code was generated by my Command Button that I was using in my VBA code. Now I see that all your results were fetched from the IfError function and Index function and Match function. I will mark this thread as solved. Thanks again!