Forum Discussion
Nesting VLOOKUP, IF and AND
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.
=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 Replies
=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.
- That is perfect. Thank you so much.
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.
