Excel Formula using IF and VLOOKUP Function

2
votes

CONTEXT

I am trying to update the roster with new players in Sheet1 using data from Sheet2. However, I am trying to only update the males roster info. I have to first verify it is a new player and also that they are male to update the height.

The logic I applied was that IF you found "Yes", and that VLOOKUP returned "Male", then the cell should be populated with a formula that would then use ID # in a VLOOKUP.

EXCEL SAMPLE

*=IF(VLOOKUP("Yes",Sheet2!C2:D6, 2, FALSE) ="Male", "=VLOOKUP(A3, Sheet2!A2:D10, 2, FALSE)", "No")

Sheet1                                         Sheet2
ID #      Height                          ID#   Height      New       Gender    
100         No                            100    66"         No        Female      
101         *                             101    72"         Yes       Male       
102         No                            102    65"         Yes       Female

PROBLEM

The output of the * is =VLOOKUP(A3, Sheet2!A2:D10, 2, FALSE) in the cell. Is there a way to get Excel to actually evaluate and populate that cell as if that was the original formula?

2
excelexcel-formulavlookup
Use the INDIRECT function – PeterT
@PeterT Could you provide some more detail please? – KL_

2 Answers

3
votes

Since you're trying to look up multiple criteria, use INDEX with MATCH instead:

=IFERROR(INDEX(Sheet2!$B$2:$B$4,MATCH(A3&"YesMale",INDEX(Sheet2!$A$2:$A$4&Sheet2!$C$2:$C$4&Sheet2!$D$2:$D$4,),0)),"No")
1
votes

You can simply take it out of quotes, but even then I don't think you'll get quite what you want.

Try this:

= IF(
    AND(
        VLOOKUP(A3, Sheet2!$A$2:$D$10, 3, FALSE) = "Yes",
        VLOOKUP(A3, Sheet2!$A$2:$D$10, 4, FALSE) = "Male"),
    VLOOKUP(A3, Sheet2!$A$2:$D$10, 2, FALSE),
    "No")