# Creating Formulas with Loops in Google Sheets that increment Cell References

So, I basically have a lookup sheet that is ever-changing in which I have various IP Addresses that I list in a dropdown list box. Depending on the selection made, I want to automate the lookup for several other fields. When I just had 3 IP addresses, i was able to accomplish this task with the formula:

`=IF(P4 = 'Lookup Values'!B3, 'Lookup Values'!D3, if(P4 ='Lookup Values'!B4, 'Lookup Values'!D4, if(P4 = 'Lookup Values'!B5, 'Lookup Values'!D5)))`

This is far from elegant and was fine for just 3 items, however a pain in the rear to update as my Lookup Values list is now about 15 IP addresses (and growing). I'm sure there's a better way to do this or perhaps a function that allows me to quickly loop through the data, find a corresponding match and then fill in an associated field.

The logic is just if P4 has a value, then it should lookup to find which item in the B column and populate this cell with the corresponding number from the D column.

Any thoughts or guidance would be highly appreciated. My brain just isn't thinking clearly. I've seen a number of loops but most of those had math functions involved and not necessarily cell references.

# Some Approaches

### VLOOKUP

``````=VLOOKUP(P4, 'Lookup Values'!B3:D, 3, 0)
``````

### FILTER

``````=FILTER('Lookup Values'!D3:D,
'Lookup Values'!B3:B=P4)
``````

### ARRAYFORMULA

``````=ARRAYFORMULA(
IF('Lookup Values'!B3:B=P4,
'Lookup Values'!D3:D,))
``````

# Function Support

• Thank you so much... 3 obvious answers are better than 1 as well. Time for me to go back and read the manual apparently. :) Commented Jan 20 at 6:24
• @2Advanced My pleasure! I learnt so much on this site from other more experienced users Stick around learn, and then answer other people's questions who are starting on the same path. Be well! Commented Jan 20 at 17:51