VLOOKUP with multiple search criteria not working.. Need Help.

[xlyfe]

Please Login or Register  to view this content.

Is there a different way that I could be going about this? Not only will excel not let me enter this as an array because a couple of the cells happen to be merged, but even if it did allow me to enter it as an array, would it even work? I've never tried to use a vlookup with 3 search criteria before.

Any help I can get is greatly appreciated!


Edit Post: I updated this post to highlight in red where I know I'm having my issue.

[rylo]

Hi

How about attaching an example file so we can see what you are trying to work with.

rylo

[FDibbins]

It appears you are looking for 3 different things. vlookup is very specific in its search - it looks for 1 thing (and 1 thing only), and returns the very 1st (and only the very 1st) that it finds.

If you are looking for something that will...find this, else find that, else find the other, you might try a series of nested vlookups with error trapping. If you are trying to find a combination of those 3, try combining them for the search, andthen adding a helper column in your data that combines the same 3 things

If all else fails, as suggested above, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that

[xlyfe]

Here they are. They may not communicate properly because of the template being located in a different location now, but if you look at the macro associated with the Create Sheet button in the CRE Database.xlsm file, maybe you can figure out what's going on so far.

Long story short, I'm trying to get cells G8:J11 in the CRE Template.xlsm file to pull data from the 3rd, 4th, 5th & 6th column's in the PlanPriceLockDate table locacted within the CRE Database.xlsm file, respective to the values needed.

Please let me know if you have any other questions for clarity. I really appreciate you taking your time to look at this for me. I've been going at it for hours using index, match, sumif, sumproduct, etc., but to no avail. I'm stumped.

[xlyfe]

It appears you are looking for 3 different things. vlookup is very specific in its search - it looks for 1 thing (and 1 thing only), and returns the very 1st (and only the very 1st) that it finds.

If you are looking for something that will...find this, else find that, else find the other, you might try a series of nested vlookups with error trapping. If you are trying to find a combination of those 3, try combining them for the search, andthen adding a helper column in your data that combines the same 3 things

If all else fails, as suggested above, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that

Hello FDibbins,

I replied with attachments before I read your reply. I appreciate you taking the time to try and help me out with this. I will note that all information within those files have been stripped of sensitivity.

I really look forward to seeing what you or rylo can come up with. As I stated earlier, please don't hesitate to ask me questions that could further clarify what I'm trying to do.

Thank you so much, again.

[rylo]

Hi

Assuming that you have both the files open at the same time, then
Template!G8: =INDEX('[CRE%20Database(1).xlsm]Project A'!$Y:$Y,SUMPRODUCT(--('[CRE%20Database(1).xlsm]Project A'!$W$1:$W$9=I3),--('[CRE%20Database(1).xlsm]Project A'!$X$1:$X$9=J2),--('[CRE%20Database(1).xlsm]Project A'!$AC$1:$AC$9=D13),ROW('[CRE%20Database(1).xlsm]Project A'!$W$1:$W$9)))

Update this for the relevant file names (they seem to get corrupted when they open on my side) and then copy down to the relevant cells, and update the index component to be the relevant column (z, aa, ab).

It does assume that the database WILL have an existing entry for the nominated parts.

HTH

rylo

[xlyfe]

Hi

Assuming that you have both the files open at the same time, then
Template!G8: =INDEX('[CRE%20Database(1).xlsm]Project A'!$Y:$Y,SUMPRODUCT(--('[CRE%20Database(1).xlsm]Project A'!$W$1:$W$9=I3),--('[CRE%20Database(1).xlsm]Project A'!$X$1:$X$9=J2),--('[CRE%20Database(1).xlsm]Project A'!$AC$1:$AC$9=D13),ROW('[CRE%20Database(1).xlsm]Project A'!$W$1:$W$9)))

Update this for the relevant file names (they seem to get corrupted when they open on my side) and then copy down to the relevant cells, and update the index component to be the relevant column (z, aa, ab).

It does assume that the database WILL have an existing entry for the nominated parts.

HTH

rylo

Just gave it a go rylo. And no luck. I'm trying to work some INDIRECT's in there because I need 'Project A'! to be replaced with %C3%, as this form needs to be adaptive in a variable sense. INDIRECT would achieve that for me, correct?

Anyways, thank you so much for helping me out with this.

Here's my modified version of what you worked out for me.

Please Login or Register  to view this content.

Should I be attempting to infuse some INDIRECT's in there? And if so, how do I go about doing that?

--------------------------------------

Edit Post: After re-reading what I just posted here, I have to clarify something. Your formula did work perfectly rylo! As long as I use the exact name of the tab that the database contents is located on. What I need now tho, is to figure out how to correctly supplant Project A'! with the contents of cell %C3% from the template file, as that cell will always have the correct name of the database worksheet needing to be referenced at that time.

Thank you again for everything!

[xlyfe]

I figured it out it finally. Thank you rylo for guiding me in the right direction.

Here's the formula that I came up in case somebody runs across this same issue.

Please Login or Register  to view this content.