Forum Discussion
Using data
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.
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.
when i do as you suggest, i get this error:
XLOOKUP is only available in recent versions of Excel. If you have an older version, try
=IFERROR(VLOOKUP(C2, Sheet2!$A$2:$B$10000, 2, FALSE), "")
