If Statement with vlook up

[kumar1803]

Hello

Please assist me in setting up a formula for if statement alongwith vlookup. Here are two tables: I would like to populate PAN number on the sheet 2 if my PNI and First Name Matches, If not then I want none value as a result.

Sheet 1


[TABLE="width: 221"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]PNI[/TD]
[TD]PAN[/TD]
[TD]First Name[/TD]
[/TR]
[TR]
[TD="align: right"]1003000720[/TD]
[TD="align: right"]2560646[/TD]
[TD]Otniel[/TD]
[/TR]
[TR]
[TD="align: right"]1003000902[/TD]
[TD="align: right"]1614854[/TD]
[TD]Jaivanti[/TD]
[/TR]
[TR]
[TD="align: right"]1003001058[/TD]
[TD="align: right"]2622283[/TD]
[TD]Ayumi[/TD]
[/TR]
[TR]
[TD="align: right"]1003001298[/TD]
[TD="align: right"]1584144[/TD]
[TD]Kasey[/TD]
[/TR]
[TR]
[TD="align: right"]1003001736[/TD]
[TD="align: right"]3237787[/TD]
[TD]Ronaldo[/TD]
[/TR]
[TR]
[TD="align: right"]1003002817[/TD]
[TD="align: right"]1635460[/TD]
[TD]Nayvis[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="width: 221"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]PNI[/TD]
[TD]First Name[/TD]
[TD]PAN[/TD]
[/TR]
[TR]
[TD="align: right"]1003000720[/TD]
[TD]Otniel[/TD]
[TD]Vook up[/TD]
[/TR]
[TR]
[TD="align: right"]1003000902[/TD]
[TD]Jaivanti[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1003001058[/TD]
[TD]Ayumi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1003001298[/TD]
[TD]Kasey[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1003001736[/TD]
[TD]Ronaldo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1003002817[/TD]
[TD]Nayvis[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[tygrrboi]

In Sheet 2 C2 (underneath the PAN heading)

try

=IFERROR(INDEX(Sheet1!$A$2:$C$7,MATCH(1,(Sheet1!$A$2:$A$7=A2)*(Sheet1!$C$2:$C$7=B2),0),2),"NO MATCH")

Input with CONTROL + SHIFT + ENTER and not just enter.
If done correctly there will be curly brackets {} around the formula. Do not manually input these brackets.
{=IFERROR(INDEX(Sheet1!$A$2:$C$7,MATCH(1,(Sheet1!$A$2:$A$7=A2)*(Sheet1!$C$2:$C$7=B2),0),2),"NO MATCH")}

explained

Sheet1!$A$2:$C$7 your whole table excluding headings
Sheet1!$A$2:$A$7 the range where all the PNI numbers are listed on sheet 1
A2 the PNI number you're looking up on Sheet 2
Sheet1!$C$2:$C$7 the range where all First Names are listed on sheet 1
B2 the First Name you're looking up on Sheet 2
2 the second column of Sheet 1, where the PAN numbers are stored
"NO MATCH" What is displayed when the PNI and First Names do not match anywhere

So the MATCH is looking for 1 (true) in the context of a PNI number matching something on sheet1 AND (*) the First name matching the same thing on Sheet 1.
If PNI matches its true = 1 If First Name matches its True = 1 so 1 * 1 = 1 and a match. If either or both of those conditions is false the result will be 0 and not a match.

The Match function will return the number where these two matches happen. Using index, then we look at the array of the whole table, get the row number (which is the matched number, and the column number which we defined as 2... (where the PAN numbers are stored).