Using data

Question

I have DATA in sheet 1 and DATA in sheet 2. I am trying to bring the data from the Pods column in sheet 2 that corresponds to the Comment column in sheet 1 and i can't seem to figure out the correct formula or command to accomplish this. any suggestions?

hansvogelaar · Answer

Option 1:
In D2 on Sheet1:
=XLOOKUP(C2, Sheet2!$A$2:$A$10000, Sheet2!$B$2:$B$10000, "")
and then fill down.
Option 2:
Assuming that the data on Sheet1 extend to row 250, enter the following formula in D2 on Sheet1:
=XLOOKUP(C2:C250, Sheet2!$A$2:$A$10000, Sheet2!$B$2:$B$10000, "")
This will spill to D2:D250.

laundryguy · Answer

when i do as you suggest, i get this error:&nbsp;

m_tarler · Answer

and with the newer TRIMRANGE you can make that sheet1 range arbitrary large with the TRIMRANGE function or just use the operator/shortcut (.):

=XLOOKUP(C2:.C99999, Sheet2!$A$2:$A$10000, Sheet2!$B$2:$B$10000, "")

Alternatively I higly recommend using TABLES (Home -> Format as Table). Then if you label the Table on Sheet 1 as "DATA" and the Table on Sheet 2 as "PODS" (you can name the table by after clicking and setting the data range a Table you then click on the 'Table' menu and edit the Table Name field). The you have:

=XLOOKUP([@Comment], PODS[Comment], PODS[Pods], "")

and then it automatically expands (and contracts) with the amount of data in the corresponding Tables AND it is READABLE so you know you are looking up the 'Comment' on this line in the Table called PODS for the corresponding 'Comment' and return the corresponding 'Pods' value. You don't have to hunt for columns C on this sheet and A and B on some other sheet.

pankajbadoni · Answer

As per your sheet, use the below in the D2 cell and apply it to all D cells&nbsp;=XLOOKUP(C2, Sheet2!A:A, Sheet2!B:B, "")&nbsp;

flexyourdata · Answer

Assuming you don't have more than 100000 lookup items on Sheet2, this represents your lookup range:=Sheet2!A2:.B100000Which is to say, from row 2 to the last non-empty row in columns A and B when considered together.&nbsp;So, using LET and XLOOKUP, and assuming you don't want to use a Table, place this formula in cell D2 of Sheet1:=LET(
  lookupData, Sheet2!A2:.B100000,
  lookupArray, TAKE(lookupData,,1),
  returnArray, TAKE(lookupData,, 2),
  XLOOKUP(Sheet1!C2:.C100000, lookupArray, returnArray, "Comment ID not found")
)&nbsp;

Forum Discussion

Using data

6 Replies

Resources