If two cells contain specific values, portray data in third cell

New Contributor

Hoping y'all can help me write a formula because I'm pretty new to excel. What I've done is created a web query to track live costs for magic the gathering cards, so I can catalog my collection and see the market prices. Mind you, I'm working with a LOT of data - 18,563 rows and 8 columns from my web query.  What I did was Insert function > lookup > Lookup value, vector, result > and it seemed to work just fine. Problem I ran into is that cards with the same name have been reprinted with various pricing. What I want to do is have it lookup the card name and the set name at the same time, to then pull the market price. Is this possible? I'll attach screen shots incase this doesn't make any sense. Thank you all in advance!


This is the function I'm using to pull the market price when I fill in the card name


And this is the result of that function


Problem is as you can see, "Opt" appears multiple times, and they have different prices, so I want the "Cost Per" cell to match the "Name" cell AND the "Set" cell before pulling the cost



4 Replies



Is this similar to what you are looking for? The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.

pull price.JPG



=INDEX(Table4_2[Market Price]:Table4_2[Market Price],MATCH(1,(Table4_2[SET]:Table4_2[SET]='TEST FORMULA OWNED CARDS'!J:J)*(Table4_2[PRODUCT]:Table4_2[PRODUCT]='TEST FORMULA OWNED CARDS'!M:M),0))

@Quadruple_Pawn This looks like exactly what I'm trying to do. I did my best to replicate your formula replacing your relevant values with the corresponding values in my book, but without looking, receiving #N/A even though the data exists. I'd be willing to send you my Excel sheet to look at directly if you have the time and would be willing, but I'll add screen shots of how my formula looked, and the relevant sheets. "Table 4_2" is the data from the web query, and "test formula owned cards" is the sheet I'm pulling data to.





And this is the data showing it exists




best response confirmed by JakeSWM217 (New Contributor)


=INDEX(Table4_2[Market Price]:Table4_2[Market Price],MATCH(1,(Table4_2[SET]:Table4_2[SET]='TEST FORMULA OWNED CARDS'!J2)*(Table4_2[PRODUCT]:Table4_2[PRODUCT]='TEST FORMULA OWNED CARDS'!M2),0))

Does this formula return the expected result of 0,13? I assume that within the "test formula owned cards" sheet we have to reference single cells like this:


Otherwise you can send me your file without sensitive data and i'll try to help.

YES! Thank you so much! I had the idea to build this data sheet a week or so ago, having never even opened excel before. My original test of 1 set worked perfectly, but this large scale issue has held me up for days. I really appreciate this