Forum Discussion
Solved
If two cells contain specific values, portray data in third cell
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
=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:
'TEST FORMULA OWNED CARDS'!J2.
Otherwise you can send me your file without sensitive data and i'll try to help.
=INDEX($I:$I,MATCH(1,($D:$D=L2)*($F:$F=N2),0))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.
=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))OliverScheurich 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
=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:
'TEST FORMULA OWNED CARDS'!J2.
Otherwise you can send me your file without sensitive data and i'll try to help.
