So I have a list of stores 1 - 13 that are in my range of A32:B55
In my cell, I need to see if that store number is listed in 2 different columns.
Here's my formula for one column only, but I want it to give me "both" if the store number is listed in 2 different columns (not just F19 in this case), and "missed" if only in 1 column or neither column. So I need to include the "If(AND somewhere, just not sure how to make that work.
=IFERROR(VLOOKUP(F19,$A$32:$B$44,2,FALSE),"MISSED")
Thank you.
Last edited by KirbyKat; 04-17-2019 at 12:40 PM.
Try this:
Does that give you what you're looking for?
Please Login or Register to view this content.
No. I changed one of the cells to E19 and kept the F15, but all of my results come up as "missed", even if they're both matches to my store numbers.
I had edited my original post fyi, sorry if you missed it. Changed the vlookup range to a32:b55, and put the word both" in all the B column cells.
Can you attach a workbook with a sample of your data and expected results? That will greatly help in finding a solution.
To attach a workbook, click Go Advanced, then scroll down a bit to Manage Attachments. Click Browse to find your file, then click Open, and finally click Upload. Click Close this window, and your file will be attached when you submit your reply.
So here's my new formula, but it's all still coming up "missed"
=IFERROR(IF(AND(MATCH($E15,$A$32:$B$44,2),MATCH($F15,$A$32:$B$44,2)),"BOTH"),"MISSED")
MATCH doesn't work with an array that is more than one cell wide.
Please attach a workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
see attachment
I think you need this:
=IFERROR(IF(AND(MATCH($D4,$A$21:$A$33,0),MATCH($D4,$C$4:$C$10,0)),"BOTH"),"MISSED")
You have not understood how the MATCH function works - I suggest you read up on it.
I'm not exactly sure what cells A21:B33 have to do with your results. Based on your sample, this should work in cell E4:
Does that provide the results you're looking for?Please Login or Register to view this content.
I didn't use the MATCH originally, someone on this thread suggested it.
It does. Thank you so much!!!
It doesn't alter the fact that you are using it incorrectly, and not in the way Melvosh suggested.I didn't use the MATCH originally, someone on this thread suggested it.
As I stated earlier, you cannot have two columns of data in a MATCH array - you can only have one. So, you cannot use $A$32:$B$44, but you CAN use $A$32:$A$44.
Here's the correct syntax for what you are trying to do:
=MATCH(lookup_value,lookup_array,0)
The 0 at the end ensures that the MATCH returns an exact match.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks