AND statement using multiple VLOOKUP criteria

  • I have a spreadsheet itemizing a coin collection and would like to have a conditional formula confirm if I have a coin listed in the spreadsheet based on a checklist. My formula is working but only partially with coins that are similar. The spreadsheet is setup as such:


    YEARCOIN DESIGNVARIETYQUANTITY
    1858Flying EagleSmall Letters1
    1858Flying EagleLarge Letters1



    I have many rows of data like the ones above and in another column I have the following formula essentially checking to see:


    1. If a quantity exists for a given year

    2. That year also has a design called "Flying Eagle"

    3. That year and design also has a variety of "Small Letters"


    I replaced the cell location with the words QUANTITY, DESIGN, and quoted words to simplify the expression.

    =IF(AND(VLOOKUP(QUANTITY,$A:$D,4,FALSE)>0,VLOOKUP(DESIGN,$A:$B,2,FALSE)="Flying Eagle",VLOOKUP("Small Letters",$A:$C,3,FALSE)="Small Letters"),1,0)


    This works fantastic confirming the first coin with small letters is present, but subsequent coins are not getting confirmed present unless the match the first coin, and no I don't actually have "Small Letters" hardcoded but rather references another cell so the next VLOOKUP statement would search for "Large Letters" but tells me it's not there. I thought the TRUE/FALSE might be the cause in the VLOOKUP statement but didn't work.


    I did search the forum and wasn't able to find anything so I apologize in advance otherwise as I know this most likely has a simple solution.


    Thanks

  • i dont know if I understand what your goal is but is it you want a formula that will return how many quantity of that specific coin you have based on your records and will match the 3 criteria youre looking for?


    for example youre checking for the yellow line if you have it in your records?

    if yes you can use COUNTIFS function,

    example for this


    =COUNTIFS("YEAR COLUMN", year cell youre checking (1986), "DESIGN COLUMN", design cell youre checking, "VARIETY COLUMN", variety cell)

  • Thanks for the help! I'm not sure why I wasn't using COUNTIFS rather than VLOOKUP since I was just verifying. I was having an issue however with the Variety column if it was blank but created an extra if statement to only check columns YEAR and DESIGN if VARIETY was blank. I'm sure it's not optimized code but it seems to be working.


    =IFERROR(IF(VARIETY=0,IF(AND(COUNTIFS(A:A,Z3,B:B,AA3)),1,0),IF(AND(COUNTIFS(A:A,Z3,B:B,AA3,C:C,AB3)),1,0)),0)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!