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.

1 Answer 1


Some Approaches


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


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


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

Function Support


  • 1
    Thank you so much... 3 obvious answers are better than 1 as well. Time for me to go back and read the manual apparently. :)
    – 2Advanced
    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!
    – Blindspots
    Jan 20 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.