Hello!
I am trying to create a formula to search the value in a static cell, searching a column in another sheet for a match and returning the value in the adjacent cell to the match on the same sheet. The matches will have duplicates, which I want to remove so that I end up with a list of values in my table. I have made many attempts, all of which have failed at some step, any help would be such a relief!
I have been able to use this formula to return the first result (but copying down returns the same exact result instead of pulling the next result and so forth):
=IF(COUNTIF(Gallons!$C:$C,$B$2)>=ROWS($K6:K25),INDEX(Gallons!$B:$B,MATCH($B$2,Gallons!$C:$C,0)+ROWS($K6:K25)*0),"")
I have also found the following website which seems to detail my exact situation, but the result I get from the formula I have modified results in a blank:
Website: http://eimagine.com/how-to-return-mu...ch-or-vlookup/
The formula I have modified: =IF(ISERROR(INDEX(Gallons!$B:$C,SMALL(IF(Gallons!$C:$C='Market Tour Report'!$K$5,ROW(Gallons!$C:$C)),ROW(5:5)),2)),"",INDEX(Gallons!$B:$C,SMALL(IF(Gallons!$C:$C='Market Tour Report'!$K$5,ROW(Gallons!$C:$C)),ROW(5:5)),2))
Sheet 1 ("market tour report") where the table I am trying to populate is:
lookup value (K5)
formula cell 1
formula cell 2 (on down for a total of 20 cells)
Example of Sheet 2 ("Gallons") where data is being retrieved (column B & C):
Data to report back (gallons B) /Data to match to lookup value (Gallons c)
report back if C2=K5 /if matches lookup report back B2
more return values (to ~70,000) /more match values (to ~ 70,000)
Bookmarks