Excel Formula Help

Question

Hey. I have been trying to create a formula for days but I’m stumped. It seems like a rather easy one too. So I have a reference table in rows from B8 to B9999 and from Z8 to Z9999. For example:

B8: Produce C8: Apples D8: Red E8: $1

I want to be able to be able to put a number into C5 and if it matches anything in the C column from C8 to C9999, it copies the entire row of information. Using the table above to offer further explain, pretty much I need it be able type in “Apples” into C5 and it find the row in the reference table (in this case, row 8) and it copy the information into Row 5

C:5

AUTOFILL

B5: Produce C5: Apples D5: Red E5: $1

So copy one row and paste the entire row into a different row.

Answer 1

No, XLOOKUP is available in Microsoft 365 and Office 2021. You can use INDEX/MATCH instead.

In B5:

=IFERROR(INDEX(B$8:B$9999, MATCH($C5, $C$8:$C$9999, 0)), "")

In D5:

=IFERROR(INDEX(D$8:D$9999, MATCH($C5, $C$8:$C$9999, 0)), "")

Fill to the right to Z5.

Answer 2

You can add a reference to the sheet name in the formula:

=IFERROR(INDEX('Sheet 1'!B$8:B$9999, MATCH($C5, 'Sheet 1'!$C$8:$C$9999, 0)), "")

Replace Sheet 1 with the real name of the sheet with the reference info.

Answer 3

Thank you so much! Last question and I’ll be done. So in order to do this exact thing in a different sheet, how would I do that? For example, the reference info is in sheet 1 and the plug in boxes are in sheet 2. I want to type in something in sheet 2 and it fill it in from the information in sheet 1.

Answer 4

Will this work for Excel 2019? If not, what can I use in its place?

Answer 5

In B5:

=XLOOKUP(C5, C8:C9999, B8:B9999, "")

In D5:

=XLOOKUP(C5, C8:C9999, D5:Z9999, "")