Forum Discussion
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...
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.
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.