Nested vlookup with If statement

[aman2059]

Hi All,

I am struggling with one formula. I have one sheet - In this sheet(tab1), I have to get "heads" information in Column D with criteria of the following columns

- Column A(Region)
- Column B(Country)
- Column C(Line)

when match With the tab2 columns( A to C)

I want vlookup to match all columns in both tabs in the workbook and if all the column( A to C) match, then return column D( heads) value from tab2 to tab1.

I tried to make formula for this but was unsuccessful. Could anyone please help me on this.

 

[aman2059]

This is something I tried to make

=IF(I2=VLOOKUP(I2,Sheet1!A:B,2,0),IF('OOC Population'!J2=VLOOKUP('OOC Population'!J2,Sheet1!B:C,2,0),VLOOKUP(Sheet1!C:D,2,0)))

 

[Noodleski]

index(tab2$D$2:$D$10,match(tab1$a2&tab1$b2&tab1$c2,tab2$a$2:$a$10&tab2$b$2:$b$10&tab2$c$2:$c$10,0),1)

confirm with Ctrl + shift + enter.

I have assumed your tab2 holds ten lines of data. Adjust as needed.

formula above goes in cell D2 on tab 1.

 

[Aladin Akyurek]

This is something I tried to make

=IF(I2=VLOOKUP(I2,Sheet1!A:B,2,0),IF('OOC Population'!J2=VLOOKUP('OOC Population'!J2,Sheet1!B:C,2,0),VLOOKUP(Sheet1!C:D,2,0)))

Now it's hard to see what is tab1 and what is tab2. What follows is set up in terms of tab1 and tab2.

In D2 of tab1 control+shift+enter, not just enter:

=IFERROR(INDEX(tab2!$D$2:$D$100,MATCH(A2,IF(tab2!$B$2:$B$100=B2,IF(tab2!$C$2:$C$100=C2,tab2!$A$2:$A$100)),0)),"No match")

 

[aman2059]

Thank you both. Problem solved