Nesting VLOOKUP, IF and AND

[Shawn_Cook]

Hi, I have a large spreadsheet of data.  Column A is names (with multiple duplicates), Column F is a data value, and Column E is a data value, and Column D is a data value.  I have copied my list of names to column H and removed the duplicates.  I now need to create a function in column I where it does a does a VLookup to find the name(s) (in table A2:F13185) and then IF the data in Column F is equal to a specific value AND the data in column E of the same row is equal to a specific value, for the looked up value in the table, then it will return the data that is in column D of the same row to column I.  

For example:  Name in Column H3 is John Smith.  I need the data that is in column D to populate to I3 if, within the same row, column A's value is John Smith and column F's value is TR1 and column E's value is 01.

[#2 · Accepted solution]

@Shawn_Cook 

=INDEX($D$3:$D$13,MATCH(1,(A3:A13=H3)*(F3:F13="TR1")*(E3:E13=1),0))

You can try INDEX and MATCH which seems to work in my sheet.

[#3]

That is perfect. Thank you so much.

[#4]

@Shawn_Cook 

You are welcome.

=VLOOKUP(H3&"TR1"&1,CHOOSE({1,2},$A$3:$A$13&$F$3:$F$13&$E$3:$E$13,$D$3:$D$13),2,0)

With VLOOKUP you can try the above formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.