Forum Discussion

Copper Contributor

Jul 10, 2023

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

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 hav...

Formulas and Functions

SPAMandEGGS

Copper Contributor

Jul 10, 2023

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.

H2O

Iron Contributor

Jul 10, 2023

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.