Having some trouble

[Busymanjohn]

Hi guys, This should be easy, but maybe I have been staring at the data too long and I am not seeing what I should be doing ... I have a large list of data, col A has country names, col B has codes, col C has order types, etc etc. In a separate sheet I would like to be able to input a country name in A1, an order type in A2 and have a formula in B2 that would return what is in col D of the raw data based on those two criteria ,,, now the issue is that the country names appear multiple times, about 12 times, in the raw data. Make sense?

 

[bobhc]

Good day

I am sure that some one could come up with a formula/function/vba to do this. Me I would use a pivot table and "slice and dice"

 

[Busymanjohn]

Hi bobhc, a pivot table would be of little use, I have only given an example of what I need, there are multiple columns of data I need to return based on the 2 criteria, using a pivot table with this data would be as good as using a filter, besides, I want it to look "neat" ;-)

 

[NARAYANK991]

Hi ,


A simple MATCH will work ; can you clarify whether the combination of country name and order type is unique ? Or can a particular combination of these two occur multiple times ?


Narayan

 

[Busymanjohn]

Hi, I am going to test using Concantenate for the two variables, should work, but if not, I'll come back to you guys. Thanks.

 

[Hui]

Busymanjohn


If you want to return just the first match of your data from Column D

Use an Index/Match with a concatenated lookup value


If you want to return the Cumulative value from Column D which match your criteria use a Sumifs or Sumproduct style formula

 

[Montrey]

I'm guessing the codes are unique for each country line item?

Also are the values in column D numbers or text?


Sample workbook would be great so we could solve this.