Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Copper 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

JakeSWM217_0-1679725439697.png

And this is the result of that function

JakeSWM217_1-1679725582969.png

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

JakeSWM217_0-1679727309349.png

 

4 Replies

@JakeSWM217 

=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.

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))

@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.

 

JakeSWM217_0-1679778371613.png

 

JakeSWM217_1-1679778385785.png

And this is the data showing it exists

JakeSWM217_2-1679778470821.png

 

 

best response confirmed by JakeSWM217 (Copper Contributor)
Solution

@JakeSWM217 

=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.

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 <3
1 best response

Accepted Solutions
best response confirmed by JakeSWM217 (Copper Contributor)
Solution

@JakeSWM217 

=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.

View solution in original post