Search for a value across multiple sheets

[Justin Jayjohn]

All,

I am not sure if this is possible or not. I have an excel workbook with 33 worksheets (or tabs). Each worksheet contains a list of network IP addresses (x.x.x.x - x.x.x.x). I would like to have an additional worksheet (using vlookup or something) where I can enter in a new IP address range. The formula would search to see if it could find that range in one of the 33 worksheets. If found, I would like the formula to return the worksheet name & specific cell reference.

Can anyone help me with this?

Thank you,

Justin 

[#2]

Hi @Justin Jayjohn

If you want to just find whether it is exists or not in another sheet, use find function. Because you can find it in all sheets at once. No need to find by switching to each sheet.

If you want to get result where it is found, you need VLOOKUP (or) COUNTIF & ADDRESS . But needs lengthy formula. Coz you have 33 sheets.

[Or]

A Macro.

Clarify the following.

1. If we use VLOOKUP/ COUNTIF function, it will ask 2 things. In which sheet & which column you are going to type IP & In 33 sheets which column you have IPs.

Send example of sheets with dummy IPs like 111.111.1.1, 123.123.1.23, 999.999.9.9