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:
YEAR | COIN DESIGN | VARIETY | QUANTITY |
1858 | Flying Eagle | Small Letters | 1 |
1858 | Flying Eagle | Large Letters | 1 |
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