VBA Code for XLOOKUP Using Multiple Criteria That Are Dynamic

Hello,

I need help writing a VBA code for macro that will perform an XLOOKUP formula based on three lookup values from a return array that is a sub-total. Since the return array is a sub-total, I have to perform "Select Visible Cells" to paste the data into a new sheet in order to do my XLOOKUP formula. I would like to know if there's a way to do the XLOOKUP based on the visible cells of the return array, so that I don't have to paste the data into another spreadsheet.

Also, I would like the macro to only run the XLOOKUP formula if the column header contains a specific name that I designate.

The original version of the workbook has thousands of rows, so the lookup value combination will change as we go through each row, making it dynamic. Below are print screens of my data with my formula in Excel, as well as the attached sample workbook.

Many thanks for assisting me with this!

You don't necessarily need VBA. Try:

Formula:

Please Login or Register  to view this content.

Adjust the NewData ranges as required but avoid full column references.

Your XLOOKUP is equivalent to this formula, if you remove sutotals from 'NewData' sheet.
=SUMIFS(NewData!L$2:L$23, NewData!E$2:E$23,B2, NewData!F$2:F$23,E2, NewData!G$2:G$23,I2)
This formula don't need 'NewData2' sheet.
so, simply enter this formula or VBA coding like below.

Please Login or Register  to view this content.

So, I get negative rep for trying to help you?

It did not answer my question.

Well, thanks for that. I won't be rushing to try to help you in the future.

Didn't your mother teach you: if you can't say something nice, say nothing.