Lookup multiple columns return a value

[mudyo22]

Hi Gurus,

Here are two sheets:

Sheet1
system ip1 ip2 ip3 ip4 ip5 ip6
system1 1.1.1.1 1.1.1.2 1.1.1.3 1.1.1.4 1.1.1.5 1.1.1.6
system2 2.2.2.2 2.2.2.3 2.2.2.4 2.2.2.5 2.2.2.6 2.2.2.7
system3 3.3.3.1 3.3.3.2 3.3.3.3 3.3.3.4 3.3.3.5 3.3.3.6


Sheet2
ip system
1.1.1.3
2.2.2.3
3.3.3.6
3.3.3.1

Sheet 1 has 7 columns(system,ip1,ip2,ip3,ip4,ip5,ip6 and ip7)
Sheet 2 has 2 columns (ip,system)
I have to fill column "system" in sheet 2 with "system" listed in column 1 of Sheet1.

In other words look for "ip" in Sheet2 in 6 columns of Sheet1 and return column 1 of sheet1 as value.

Thanks much!

 

[pplstuff]

Sheet2!b2: =if(match(a2,sheet1!B:B,0)=na(),"",index(sheet1!A2:A4,match(a2,sheet1!B:B,0))&if(match(a2,sheet1!C:C,0)=na(),"",index(sheet1!A2:A4,match(a2,sheet1!C:C,0))

Essentially, it's searching colB, if there's a match return the system. than it goes to the next column.

Keep in mind I set this up for A:A and B:B, copy and paste over again to account for all your columns. Add punctuation if needed. And change "a:a" "b:b" to absolute references etc.

Let us know if you need more advice, but this should be a good start.

 

[njimack]

Excel Workbook
	A	B
1	ip	system
2	1.1.1.3	system1
3	2.2.2.3	system2
4	3.3.3.6	system3
5	3.3.3.1	system3
Sheet2

 

[iyyi]

Using a UDF can make things easier.
The next UDF receives 3 parameters:
1. The Item you want to find
2. The range you search
3. (optional) The result type the function returns: 1=row, 2=column, all others = the cell address
You can combine this UDF in an INDEX function to achieve what you want.

The UDF code:
Function SearchRange(Item, InRng As Range, Optional Ans As Integer)
For Each cell In InRng
If cell = Item Then
Select Case Ans
Case 1: SearchRange = cell.Row
Case 2: SearchRange = cell.Column
Case Else: SearchRange = cell.Address
End Select
Exit Function
End If
Next
SearchRange = CVErr(xlErrNA)
End Function

 

[Oeldere]

@iyyi

Please add code between code tags ##

 

[mudyo22]

Thank you pplstuff,njimack,iyyi,Oeldere for the replies, it helped a lot.

njimack - Formula worked like a charm, thank you.