-1

I need to match two conditions on the cell Name and add the price information into cell price if both condition match. In other words, if Name contains both conditions, get the price. I tried different approaches using QUERY, SEARCH; FIND; VLOOKUP but I got stuck somewhere in the middle. Here's the example sheet (Google Spreadsheet solution preferred over Excel):

https://docs.google.com/spreadsheets/d/1zwG3_5Ctg_IZ1kI04Uee-qIvMrNQ4GmEwySmYcMKLfA/edit?usp=sharing

Maybe important: Both, the Name values as well as the whole reference table get pulled from other files dynamically. So I don't know anything concerning order or length of these columns in advance, not even if there are matches at all.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Christian Borck
  • 1,812
  • 1
  • 13
  • 19
  • based on your update note, then you need to use my answer, because it doen't take the length of the string into account. it could be anything, ref and names; – Marcel Apr 01 '15 at 07:59

2 Answers2

1

Based on your spreadsheet table:
you can try the following formula:
this formula works in excel not in google spreadsheets

=IFNA(INDEX($G$2:$G$6;MATCH(1;COUNTIFS(A2;"*"&$E$2:$E$100&"*";A2;"*"&$F$2:$F$100&"*");0));"NOT FOUND")



this is an array formula, so press ctrl+shift+enter to calculate the formula.

i think it will do the job.
here is the example file to download

Marcel
  • 2,764
  • 1
  • 24
  • 40
  • If I put this formula in e.g. cell `B2` it doesn't work (in Excel, array formula activated)? Did I miss something? – Christian Borck Apr 01 '15 at 08:24
  • @ChristianBorck: maybe your excel delimiter is , and not ; as in my answer. here it is =IFNA(INDEX($G$2:$G$6,MATCH(1,COUNTIFS(A2,"*"&$E$2:$E$4&"*",A2,"*"&$F$2:$F$4&"*"),0)),"NOT FOUND") – Marcel Apr 01 '15 at 08:27
  • Thanks, but that doesn't seem to be the problem, my Excel expects `;` – Christian Borck Apr 01 '15 at 08:32
  • @ChristianBorck: I added an example file for you, download it and see if it works. By the way, check if your formula options are on automatic calculation in your excel options. – Marcel Apr 01 '15 at 08:41
  • Thank you, your linked file works, the problem was a local language issue of formular expressions. I updated my example file as well. Now your file is still working for all but cell `B4`. – Christian Borck Apr 01 '15 at 12:02
  • 1
    @ChristianBorck: actually, because my range was from E2 to E4, and F2 to F4, so I just updated it to E2:E100 and F2:F100, you need to adjust this to fit your range in. I also updated the download file, you can see the changes in the new file. – Marcel Apr 01 '15 at 19:53
1

In addition to previous answer AND given the current set of data (in a Google spreadsheet), in B2 try:

=ArrayFormula(iferror(vlookup(regexreplace(A2:A; "[^A-Z]"; ""); {E2:E&F2:F\G2:G}; 2; 0)))

and see if that works ?

JPV
  • 26,499
  • 4
  • 33
  • 48
  • Nice, this is almost what I was looking for. But If I get this right, you are extracting all Capital Letters in `Name`. Maybe my example is misleading, as I don't know anyting about the structure of text in `Name` column. It will be a mix of lower, upper case, numbers, underscores at least. So the crucial thing is to check if the conditions are contained. – Christian Borck Apr 01 '15 at 09:55
  • The formula is built around the matching of capitals. If the names will not have capitals, we need to find something else. Will it be posible that a name solely consists of lower case letter or numbers ? Will the pattern to be matched always be capital letters ? – JPV Apr 01 '15 at 10:05
  • If possible, add some more examples (with all the possible 'types' of names) to your example sheet. – JPV Apr 01 '15 at 10:06
  • I updated the examples in the spreadsheet. Hope it's more clear now. Thank you! – Christian Borck Apr 01 '15 at 11:48
  • Is there some kind of logic that can be used in the regular expression ? – JPV Apr 02 '15 at 07:53
  • Unfortunately the only 'logic' would be: Are both conditions strings included in the `Name` column string. – Christian Borck Apr 04 '15 at 08:03