VLOOKUP Multiple Columns

[excelhelp2011]

Hi Gurus,

I have following sample data
Sheet1
IPAddress Systemname
1.1.1.1
2.2.2.2

Sheet2
Systemname IP1 IP2 IP3
System1 2.3.3.3 2.4.4.4 2.2.2.2
System2 1.2.2.2 1.3.3.3 1.1.1.1

How can I get the systemname in Sheet1 by matching "IPAddress" in Sheet1 to IP1,IP2,IP3 in Sheet2

Thanks much

 

[Aladin Akyurek]

Hi Gurus,

I have following sample data
Sheet1
IPAddress Systemname
1.1.1.1
2.2.2.2

Sheet2
Systemname IP1 IP2 IP3
System1 2.3.3.3 2.4.4.4 2.2.2.2
System2 1.2.2.2 1.3.3.3 1.1.1.1

How can I get the systemname in Sheet1 by matching "IPAddress" in Sheet1 to IP1,IP2,IP3 in Sheet2

Thanks much

B2, Sheet1...

Control+shift+enter, not just enter, and copy down:

=IF(COUNTIF(Sheet2!$B$2:$D$3,A2),INDEX(Sheet2!$A$2:$A$3,
   MIN(IF(Sheet2!$B$2:$D$3=A2,
    ROW(Sheet2!$A$2:$A$3)-ROW(Sheet2!$A$2)+1))),"")