Jul 09 2023 05:00 PM
Hello Everyone,
So I have been working on this sheet. I fairly novice and have some things working the way I want to.
Essentially, I'll just be very deceptive. I am making a tracker and have a list of department codes and job codes. For this function I want the cell to be able to have a result of what is required for each position depending on their job position. The issue I am having is there are duplicate codes obviously for each department and job. I believe that's where the error is coming up. I just can't figure it out. Am I even using the right function? XLOOKUP? Should I be using something else? I have looked all over and spent days on this I cannot figure it out.
This is a little screen shot of what I gave up on. I tried so many different combinations and made so many different groups of data that I could not think of any names any more for the group names.
Hoping I can find some help here and hope I have given enough information.
Thank you in advance.
Jul 09 2023 06:29 PM
Jul 09 2023 06:52 PM
Hello @H2O.
Thank you for replying. If you'd like to take a look I can show you. The boolean method is a little confusing to me with using the example of sales.
I have a list of job does, department codes, required work cards (per position). Here is a small screen shot.
So the in the screen shot above will be entered into the sheet I created shown below.
Department Code into D3, Job Code into E3 then F3:H3 generate automatically based upon the info inputted. Each Title in G3 require different work cards T3. I am trying to formulate a string that will take the info entered into D3 & E3 to correlate it with the information from the first screen shot G1.
I know it is XLOOKUP I just can't figure out the string or what needs to be created for the lookup_array/return_array.
Thank you again.
Jul 09 2023 08:09 PM
Jul 09 2023 10:07 PM
If there is only one Required Work Card per department-position combination, then yes, XLOOKUP is an appropriate function to use.
It would have been useful in communicating if you had:
There are at least three ways of referencing the lookup data within the XLOOKUP (or other lookup) function:
Examples of the first two techniques are shown in the attached workbook, It seems your LU-Range data is not in an Excel table (column G apparently has no column header), so I did not bother coming up with an example of the third technique.
Note that named ranges can be written so as to automatically expand/contract as rows are added/removed; these are dynamic named ranges, as described here and here.
Jul 10 2023 01:22 AM
Jul 12 2023 05:16 AM