Vlookup Excel - If first lookup value is empty then formula continues to find

VicJW 0
Viewed 23

I found a solution here (from April 2020) but can't get it to work. I have a column containing a ParcelID which may contain duplicates (it refers to a land parcel which may get sold numerous times). A second column has the property address. In some cases the propertyaddress is blank. I want to be able to use the ParcelID to find an occurence where the propertyaddress is listed and fill this into the blank cells for that PropertyID. The attached extract from the workbook shows the issue. I inserted a new column E where I entered the following formula: =IFNA(VLOOKUP(A2,'[nash_clean_excel_project (1).xlsx]Raw Data'!A1:D56478,4,1),VLOOKUP(A2,'[nash_clean_excel_project (1).xlsx]Raw Data'!A1:D56478,4,0)) As you can see, it has filled cells E18 and E19 with 0 and not the address from D18

Thanks in advance

Sample data

excel-formula excel
1 Answers

Office 365: @FILTER([nash_clean_excel_project (1).xlsx]Raw Data'!A1:D56478,([nash_clean_excel_project (1).xlsx]Raw Data'!A1:A56478=A2)*([nash_clean_excel_project (1).xlsx]Raw Data'!D1:D56478<>""))

@ in front of the formula results in the first result only. Otherwise it would spill each match.

Older Excel versions: =INDEX[nash_clean_excel_project (1).xlsx]Raw Data'!D1:D56478,MATCH(1,([nash_clean_excel_project (1).xlsx]Raw Data'!A1:A56478=A2)*([nash_clean_excel_project (1).xlsx]Raw Data'!D1:D56478<>""),0))

Entered with ctrl+shift+enter would check for the row of column A where it matches the search value and the value in column D is not empty

P.b0
Related