Forum Discussion
Have a cell populate with data depending on what's inputted in 2 other cells. XLOOKUP?
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.
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.
- 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. - Since this is all about Tables and Relationships (between tables) I'd suggest this is an ideal candidate for power query and power query
