Forum Discussion
Looking for Xlookup with IF function
Hi!
I'm trying to find a formula that allows me to search for a sku in column E but I need the return to be based on whether or not the cell in column F says True or False.
If it's true, I need to return the sku that includes a letter.
If it's false, I need to return the sku that does not include a letter.
I have 2 line items for the same Item Number, but one includes pots and the other one doesn't. If I just do a normal xlookup, the return doesn't work the way I need it to. Help?!
I hope this snip helps. In column H you can see that the return is the same for both lines, the top line should have a -P in it.
10 Replies
Not sure if the first response saved....On a test file I came up with the following
=INDEX(G:G,MATCH(1,IF(E:E=E16,IF(F:F=F16,1)),0)) (i.e., E16 ~ item number and F16 ~ "True" or "False" as required)
Thank you so much MKoski!!! That works! This is amazing, we were splitting reports to separate the pots and that part was confusing people who aren't comfortable with excel. Much appreciated, I hope you have a great day!
This worked for me in a test file...
=INDEX(G:G,MATCH(1,IF(E:E=E16,IF(F:F=F16,1)),0))Why same formula two times?
I need to return the Final Sku on all lines in that column, usually there are about 20 lines. The same flowers are sold with pots as without pots which is why the skus are the same, only difference is the P. I keep getting the "flowers" sku returning on all line no matter if there are pots or not. I hope that makes sense. LOL
It's still confusing - originally you had the same Item Number in E5 and E6, now you have different ones, so a straight XLOOKUP will work.
I believe I made a mistake when I created the snip. Hopefully this one is better. I used the same formula twice because I need the same return on all lines in that column (usually there are about 20 lines).
--------------------------------------------------------------------------------------------------------------------------
This is confusing. A formula in column H cannot look up something in the entire column H - that causes a circular reference. Perhaps you simplified the example too much?
It looks like my reply didn't save...
Maybe this can help, I'm not great at formulas.
-------------------------------------------------------------------------------------------------------------------
