2 Criteria to Determine What Data Set to Use During VLOOKUP
I'm trying to find a value in a table based on matching the subject column to B2, quarter column to B4 and the value in the specified option column above 0. B2 and B4 have drop down lists so that text is variable and would change the chosen selection.
This was the simplest way I could think to describe without leaving out important information.
I've done hours of research at this point to figure out the correct formula and I've had no luck.
Cell B7 formula , drag right
Formula:
Please Login or Register to view this content.
Gives a #N/A error. Can you explain how this process is working?
From my understanding it's looking up values for B2 and B4 but the IF is confusing me. I don't understand why "$A$10:$A$25&$F$10:$F$25" is being used in the "value if true field" and "B$10:B$25" for "value if false" field. Doesn't it need a table selection to be able to match across the row?
Currently I have it searching for Subject 1, Quarter 1 where the value shows 2 in the table but it does not convert over.
I've simplified what was mentioned in the last response I had and thought it worked but it still won't pull from the Quarter selection field.
Formula:Please Login or Register to view this content.
The multiple selection is why I was attempting to use COUNTIFS but that didn't seem to work based on text inside a field.
This test this file , No #N/A errorOriginally Posted by xDarkDeadpool
It probably needs entering with CSE in Excel 2019.
CSE = CTRL+SHIFT+ENTER before copying across.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thank you, ALIGW. You are right. CSE=CTRL+SHIFT+ENTER, I haven't used the old version for too long and almost forgot about their existence
Me, neither - just remember that any array formula needs it in anything OLDER THAN 2021 - no CSE for 2021 and 365 (or 2024 going forward).
Thank you both. That works now. What is the purpose of the CSE function?
Because it's an array formula, Excel 2019 and earlier need it entering with CSE to prevent error messages - it signals to Excel that the formula needs handling as an array. In 2021 and newer (inc. 365), this is no longer necessary - Excel can detect array formulae without a nudge.
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
@xDarkDeadpool You're Welcome. Glad to help . Thank You for the feedback and rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks