if cell B1 is blank, find the next cell in column A with matching data that also has data in column B. Input the data found in column B into B1

Question

In column A I have multiple ID numbers, and most of them have duplicates.

In column B I have "Areas" associated with the ID number, but only one ID out of the duplicates has this associated "Area".

This leaves multiple blanks in column B that do not have "Areas" assigned. I need the blank cells in column B to find their matching IDs in column A that have an Area associated with it, and input this data into the blank cell.

My knowledge of Excel is limited, but where there's a will there's a way.

I tried to use CHAT GPT as a way of finding an answer with no result.

=IF(B1="", INDEX(B:B, MATCH(TRUE, (A:A=A2)\*(B:B\<\>""), 0)), B1)

Here is an example of the table that I am trying to create a formula for:

Column A	Column B
222222
222222
222222	Area 1
555555
555555	Area 2
777777
777777
777777	Area 3

Here is how I would like the formula to work:

Column A	Column B
222222	Area 1
222222	Area 1
222222	Area 1
555555	Area 2
555555	Area 2
777777	Area 3
777777	Area 3
777777	Area 3

Any help I can get with this would be greatly appreciated. Thank you!

Answer 1

Try using XLOOKUP() and the use of visible cells

---

• Select the whole data using CTRL+*
• Press CTRL+SHIFT+L to apply filters.
• From the filter dropdown select Blanks
• From Home Tab --> Goto Editing Group --> Click Go To Special or press function key F5 click on Goto Special
• Select Visible Cells Only. (Short Cut is ALT+;)
• Enter the following formula in the first cell and press CTRL+ENTER together.

---

=XLOOKUP(A2,A2:A8,B2:B8,,,-1)

---

• Remove filter to see all applied from below.

---

Or as alternative can use this formula as well, if you don't have XLOOKUP()

=INDEX(B2:B9,MATCH(A2,A2:A9,1))

---