New Contributor

# Xlookup adding the same information to multiple lines

=XLOOKUP(\$D\$2,Sheet1!\$D2:\$D\$1000,Sheet1!\$A2:\$A\$1000,"",0,1)

I have 7 tabs (days of the week) that pull information off one sheet (all the information that needs to be sorted). I can get it to pull the information based off the dates matching with the above formula. The sheet it pulls from the dates move around and sometimes there is more or less of said date. 4/3 is the first date, and it only pulls the necessary information.  4/4 starts on row 4 from the data sheet, it is bringing the same data over 3 rows above and the row it starts.  How do I make it bring the data over only one time and skip the other rows? The number of times the date is used changes weekly, like sometimes Tuesday will have 5 and other times 10.

2 Replies

# Re: Xlookup adding the same information to multiple lines

To ensure that XLOOKUP brings over the data only once and skips the other rows, you can use the IF function in combination with XLOOKUP. Here's an example formula that you can use:

=IF(\$D2<>\$D1,XLOOKUP(\$D2,Sheet1!\$D:\$D,Sheet1!\$A:\$A,"",0,1),"")

In this formula, the IF function checks if the date in the current row (\$D2) is different from the date in the previous row (\$D1). If it is, XLOOKUP is used to retrieve the data from Sheet1, and if it's not, an empty string is returned. This way, the data will be pulled over only once per date, and the other rows will be blank.

You can copy this formula to the cells where you want to retrieve the data, and adjust the references to the data sheet and the columns as needed.

# Re: Xlookup adding the same information to multiple lines

I appreciate the quick response, but this did not work. It put A2 in the correct space but under that brought over A1 and does not travel down to grab the data with the same dates.