Forum Discussion
Solved
XLOOKUP Formula Help needed in Excel
Help needed please...
I need to search for a name (i.e. Joe Smith) in columns V & X on one spreadsheet (see below) and have the CK NO on this spreadsheet be applied to another worksheet called Commission Summary (cell A6) (See second screen shot below) using XLOOKUP.
Thank you in advance!
Sandra
Join the lookup columns and make the XLOOKUP run a wildcard match.
For example (A1 being "Joe Smith")
=XLOOKUP("*"&A1&"*",Sheet1!$V$2:$V$5&Sheet1!$X$2:$X$5,Sheet1!$A$2:$A$5,,2)
Join the lookup columns and make the XLOOKUP run a wildcard match.
For example (A1 being "Joe Smith")
=XLOOKUP("*"&A1&"*",Sheet1!$V$2:$V$5&Sheet1!$X$2:$X$5,Sheet1!$A$2:$A$5,,2)The formula worked but when I want it to look up the next commission for this sales rep, it repeats the same one instead of pulling in the next one.
- Hello Patrick2788, Thank you so much! It worked on the first line, but I want to be able to pull all the data for "Joe Smith" to bring in all his commissions. So on the 2nd row, the same formula is used, thinking it would search for the next commission on the whole sheet, but it didn't. I am trying to have it look at a long list and bring in this one persons commissions made for that month.
- I'd have to see the sheet. It sounds like FILTER may be the way to go.
