SOLVED

# IF and Lookup Function

Copper Contributor

# IF and Lookup Function

Hi Guys,

I have sheets. Sheet "Vehicle Register" and "Trucks".

In the "Vehicle Register" sheet I have registration numbers in column A and Vehicle Type in column B. I need to on the "Trucks" sheet get the registration number if "Truck" is in Column B. The normal IF function leaves me with #N/A if I get to a row that has "Machine" in it. So what I need to know is how do I skip "Machine" and lookup the next "Truck" value without it returning a blank or error

"Vehicle Register" sheet

Column A   Column B

A1524         Truck

A1525         Truck
B1111          Machine
A1526          Truck

Required result on "Trucks" sheet

Column A

A1524

A1525
A1526

2 Replies
best response confirmed by Ivabanovich (Copper Contributor)
Solution

# Re: IF and Lookup Function

``=IFERROR(INDEX('Vehicle Register'!\$A\$2:\$A\$5,SMALL(IF('Vehicle Register'!\$B\$2:\$B\$5="Truck",ROW('Vehicle Register'!\$1:\$4)),ROW('Vehicle Register'!1:1))),"")``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

If you have Excel 2019 or later you can apply FILTER function.

# Re: IF and Lookup Function

You are a legend. This did exactly what I wanted it to do! Thanks
1 best response

Accepted Solutions
best response confirmed by Ivabanovich (Copper Contributor)
Solution

# Re: IF and Lookup Function

``=IFERROR(INDEX('Vehicle Register'!\$A\$2:\$A\$5,SMALL(IF('Vehicle Register'!\$B\$2:\$B\$5="Truck",ROW('Vehicle Register'!\$1:\$4)),ROW('Vehicle Register'!1:1))),"")``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

If you have Excel 2019 or later you can apply FILTER function.