Have a cell populate with data depending on what's inputted in 2 other cells. XLOOKUP?

[SPAMandEGGS]

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. 

[#2]

Based on your question, it seems you want to use XLOOKUP to return a value based on multiple criteria in different columns. This is possible, but you need to use some tricks to make it work.
One way is to use "Boolean logic" to create an array of 1s and 0s that represent the rows that match all your criteria, and then use XLOOKUP to find the first 1 in that array. For example, if you have a table of data in B5:E15, and you want to look up the price of an item based on its name, size and color, you can use this formula:

`=XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15)`

where H5, H6 and H7 are the cells where you enter the criteria. This formula will return the price of the item that matches all three criteria, or #N/A if no match is found.

Another way is to use the "CHOOSE function" to create a virtual table with two columns: one for the criteria and one for the result. You can then use VLOOKUP to find the value in the second column that matches 1 in the first column. For example, using the same data and criteria as before, you can use this formula:

`=VLOOKUP(1,CHOOSE({1,2},(H5=B5:B15)*(H6=C5:C15)*(H7=D5:D15),E5:E15),2,0)`

This formula will return the same result as the XLOOKUP formula above.

[#3]

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. 

[#4]

Since this is all about Tables and Relationships (between tables) I'd suggest this is an ideal candidate for power query and power query

[#5]

@SPAMandEGGS 

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:

• mentioned your worksheet names; I'm using Sheet1 and L-Range to represent your first (and third) and second screenshots, respectively.
• hidden Sheet1 columns H through S before making the screenshot; those columns are irrelevant to the lookup, and made it more difficult to read the relevant information.

There are at least three ways of referencing the lookup data within the XLOOKUP (or other lookup) function:

• cell-address range references (Note the use of absolute addresses, which include $. This allows cells containing the formula to be easily copied.)
• named range references
• Excel-table structured references

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.

[#6]

XLOOKUP is a powerful function in Excel that can help you look up values in a table or range by row or column. It can also be used to look up values based on multiple criteria. Here's an example of how you can use XLOOKUP to look up data based on multiple criteria:

`=XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15)`

This formula looks for the value 1 in an array created by multiplying three Boolean expressions together. The first expression checks if the value in column B matches the value in cell H5, the second expression checks if the value in column C matches the value in cell H6, and the third expression checks if the value in column D matches the value in cell H7. If all three expressions are true for a particular row, then XLOOKUP returns the corresponding value from column E.

You can use this formula to look up data based on multiple criteria in your sheet. I hope this helps.

[#7]

Hi @SnowMan55 I sent you the file to look at as I was having difficulty executing your recommendation. Check messages please.