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):
=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 form it results in a blank:
Website: http://eimagine.com/how-to-return-multiple-match-values-in-excel-using-index-match-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))
Example of Sheet 2 ("Gallons") where data is being retrieved from:
<tbody>
</tbody>
Example of Sheet 1 ("market tour report") where the table I am trying to populate is:
<tbody>
</tbody>
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):
=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 form it results in a blank:
Website: http://eimagine.com/how-to-return-multiple-match-values-in-excel-using-index-match-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))
Example of Sheet 2 ("Gallons") where data is being retrieved from:
Misc | data to report back (gallons B) | Data to match to lookup value (gallons c) | misc | misc |
report back if C2= sheet 1 lookup value | if matches lookup report back B2 | |||
more return values (to ~70,000) | more match values (to ~ 70,000) |
<tbody>
</tbody>
Example of Sheet 1 ("market tour report") where the table I am trying to populate is:
lookup value (K5) | misc | misc | misc | misc | misc | misc | misc | misc | misc |
formula cell 1 | |||||||||
formula cell 2 (on down for a total of 20 cells) |
<tbody>
</tbody>